SQL Server - Leer entre # un campo char

declare @Myvariable varchar(50) =  '#HOLA R# CAS # SAC #'

while len(@Myvariable) > 1

 begin

  select SUBSTRING(@Myvariable, 2, CHARINDEX('#', @Myvariable, 2)-2)

  select @Myvariable = SUBSTRING(@Myvariable, CHARINDEX('#', @Myvariable, 2), LEN(@Myvariable))

  continue

 end