sql Return string between two characters

Question

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.

Add if statement and check if charindex('-',@string)>0, then only execute — Atk
– Atk, Commented Mar 4, 2020 at 16:44
@Jack good question - I've updated the original post with these scenarios. — Chipmunk_da
– Chipmunk_da, Commented Mar 4, 2020 at 17:09
@Python_newbieash have a look at my answer ... it has gotten complicated, but it should cover the cases — Ja͢ck
– Ja͢ck, Commented Mar 4, 2020 at 17:23
What about matching /(?<=')[^-]*|(?<= ).*/, (?<=...) being a positive lookbehind? — Cary Swoveland
– Cary Swoveland, Commented Mar 4, 2020 at 18:04

Philip Kelley · Accepted Answer · 2020-03-04 20:08:02Z

2

Take your existing code:

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

insert one line, like so:

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

and you're done. (If NULL, you will get NULL returned. Also, this will return all data based on the FIRST dash found in the string, regardless of however many dashes are in the string.)

edited Mar 4, 2020 at 20:08

answered Mar 4, 2020 at 19:57

Philip Kelley

40.5k11 gold badges62 silver badges93 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Fabien TheSolution · Accepted Answer · 2020-03-04 19:51:33Z

1

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

edited Mar 4, 2020 at 19:51

answered Mar 4, 2020 at 19:44

Fabien TheSolution

5,0501 gold badge20 silver badges31 bronze badges

1 Comment

Chipmunk_da Over a year ago

Thanks @Fabian TheSolution! This works like a charm!

I_Tried · Accepted Answer · 2020-03-04 19:00:02Z

1

Try this. If there are two dashes, it'll take what is inside. If there is only one or none, it'll keep the original string.

  declare @string varchar(100) = 'BLAH-90ExtractThisWOW'
  declare @dash_index1 int = case when @string like '%-%' then CHARINDEX('-', @string) else -1 end
  declare @dash_index2 int = case when @string like '%-%'then len(@string) - CHARINDEX('-', reverse(@string)) + 1 else -1 end

  SELECT case 
         when @dash_index1 <> @dash_index2 then SUBSTRING(@string,CHARINDEX('-',@string)+1, CHARINDEX('-',@string,CHARINDEX('-',@string)+1) -CHARINDEX('-',@string)-1) 
         else @string end
         as My_String

edited Mar 4, 2020 at 19:00

answered Mar 4, 2020 at 16:50

I_Tried

416 bronze badges

1 Comment

I_Tried Over a year ago

Sorry about that. try it now. I accidentally erased the '-' when formatting the sql

Collectives™ on Stack Overflow

sql Return string between two characters

3 Answers 3

Comments

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related