How to select all the words between a char in sql string in sql server?

I have a table with a column with these example values

And I need to extract all the words between the #. Is it possible?

I have tried using SUBSTRING but not all the words have the exact same length.

You may create a function like this:

-- create a function
create function dbo.ufn_extract_words (
  @input nvarchar(2000), @str nvarchar(15)
) returns nvarchar(2000) as begin

  if(@input is null)
    return null;

  if(@str is null)
    return null;

  declare
    @result nvarchar(2000), @len int,
    @start int, @end int;

  select
    @result = '', -- initialize the return value, don't set to null value*
    @start = charindex(@str, @input), -- find the first match
    @len = len(@str);

  while @start > 0 begin

    -- find the next match
    select @end = charindex(@str, @input, @start + @len);
    if @end = 0 break;

    -- add a comma after the previous word, if not the beginning
    if len(@result) > 0 set @result = @result + ',';

    -- extract word
    set @result = @result + substring(@input, @start + @len, @end - @start - @len);

    -- find the next start match
    set @start = charindex(@str, @input, @end + @len);

  end

  return @result;

end;

go

declare @table table (
  Commands nvarchar(2000)
)

insert into @table (Commands) values
  ('SELECT - #TRAPAY#'),
  ('SELECT - #OSTLIA#'),
  ('SELECT ISNULL(CONVERT(VARCHAR(50), #OPCASH# + #ACCREC# + #INVENT# + #OCASS# + #TRAPAY_N# + #OSTLIA_N#), ''''-'''')'),
  ('SELECT #GTFA#'),
  ('SELECT #ACCDEP#');

select Commands, Words = dbo.ufn_extract_words(Commands, '#') from @table;

-- drop function
drop function dbo.ufn_extract_words;
go

Use Replace Function to populate only text in between the #

SELECT REPLACE('#TRAPAY#' ,'#','')
SELECT REPLACE('#OSTLIA#','#','')
SELECT REPLACE('ISNULL(CONVERT(VARCHAR(50), #OPCASH# + #ACCREC# + #INVENT# + #OCASS# + #TRAPAY_N# + #OSTLIA_N#)'''', ''''-'''')','#','')
SELECT REPLACE('#GTFA#','#','')
SELECT REPLACE('#ACCDEP#','#','')

You one String which contains multiple #. If you want in between words in separate rows, then Use STRING_SPLIT

SELECT * FROM STRING_SPLIT('ISNULL(CONVERT(VARCHAR(50), #OPCASH# + #ACCREC# + #INVENT# + #OCASS# + #TRAPAY_N# + #OSTLIA_N#)'''', ''''-'''')','#') WHERE RTRIM(value) <> ''

Below pasted both outputs

SELECT REPLACE('#TRAPAY#' ,'#','')
SELECT REPLACE('#OSTLIA#','#','')
SELECT REPLACE('ISNULL(CONVERT(VARCHAR(50), #OPCASH# + #ACCREC# + #INVENT# + #OCASS# + #TRAPAY_N# + #OSTLIA_N#)'''', ''''-'''')','#','')
SELECT REPLACE('#GTFA#','#','')
SELECT REPLACE('#ACCDEP#','#','')

SELECT * FROM STRING_SPLIT('#TRAPAY#' ,'#') WHERE RTRIM(value) <> ''
SELECT * FROM STRING_SPLIT('#OSTLIA#','#') WHERE RTRIM(value) <> ''
SELECT * FROM STRING_SPLIT('ISNULL(CONVERT(VARCHAR(50), #OPCASH# + #ACCREC# + #INVENT# + #OCASS# + #TRAPAY_N# + #OSTLIA_N#)'''', ''''-'''')','#') WHERE RTRIM(value) <> ''
SELECT * FROM STRING_SPLIT('#GTFA#','#') WHERE RTRIM(value) <> ''
SELECT * FROM STRING_SPLIT('#ACCDEP#','#') WHERE RTRIM(value) <> ''

fiddle