sql Return string between two characters

I want to know a flexible way to extract the string between two ‘-‘. The issue is that ‘-‘ may or may not exist in the source string. I have the following code which works fine when ‘-‘ exists twice in the source string, marking the start and end of the extracted string. But it throws an error “Invalid length parameter passed to the LEFT or SUBSTRING function” when there is only one ‘-‘ or none at all or if the string is blank. Can someone please help? Thanks

  declare @string varchar(100) = 'BLAH90-ExtractThis-WOW'
  SELECT SUBSTRING(@string,CHARINDEX('-',@string)+1, CHARINDEX('-',@string,CHARINDEX('-',@string)+1) -CHARINDEX('-',@string)-1) as My_String

Desired Output: ExtractThis

If there is one dash only e.g. 'BLAH90-ExtractThisWOW' then the output should be everything after the first dash i.e. ExtractThisWOW. If there are no dashes then the string will have a blank space instead e.g. 'BLAH90 ExtractThisWOW' and should return everything after the blank space i.e. ExtractThisWOW.

Answer

You can try something like this.

When there is no dash, it starts at the space if there is one or take the whole string if not. Then I look if there is only one dash or 2

declare @string varchar(100) = 'BLAH90-ExtractThis-WOW'
declare @dash_pos integer = CHARINDEX('-',@string)

SELECT CASE 
WHEN @dash_pos = 0 THEN 
       RIGHT(@string,LEN(@string)-CHARINDEX(' ',@string))
ELSE (
    CASE 
        WHEN @dash_pos = LEN(@string)-CHARINDEX('-',REVERSE(@string))+1 
        THEN RIGHT(@string,LEN(@string)-@dash_pos)
    ELSE SUBSTRING(@string,@dash_pos+1, CHARINDEX('-',@string,@dash_pos+1) - 
                                                     @dash_pos -1) 
    END
) 
END as My_String