Extract a substring from the end of a string in sql server

Question

I have following string :

DECLARE @build_names VARCHAR(5000) = NULL; 

SET @build_names = 'BB10-1_X-4759-566549'; 

i want to extract it from the last, - is the delimiter. The string will be extracted into 3 sub-strings i.e. 566549, 4759, BB10-1_X.

Please help me, I am a beginner.

Answer 1

You can use charindex() and reverse() to get your desired results:

declare @temp varchar(40)
set @temp = 'BB10-1_X-4759-566549'

select @temp, REVERSE(@temp)
select REVERSE(substring(REVERSE(@temp),0,CHARINDEX('-',REVERSE(@temp),0)))

Give this a shot. It answers your first question of extracting from after the last - of the string.

The next part of your question seems to indicate that you want to split the entire thing up based on the -. Using stored procedures or functions will fail because you want BB10-1_X to be a single string. So if these strings are always in this format of having exactly three -'s but you only want 3 substrings, you can hard code it like this.

declare @temp varchar(40), @reverse varchar(40), @sub1 varchar(20), @sub2 varchar(20), @sub3 varchar(20)

SET @temp = 'BB10-1_X-4759-566549'
SET @reverse = REVERSE(@temp)

SET @sub3 = REVERSE(substring(@reverse,0,CHARINDEX('-',@reverse,0)))

SELECT @temp = substring(@temp,0,charindex(REVERSE(substring(@reverse,0,CHARINDEX('-',@reverse,0))),@temp,0)-1)
SELECT @reverse = REVERSE(@temp)

SET @sub2 = REVERSE(SUBSTRING(@reverse,0,CHARINDEX('-', @reverse, 0)))
SET @sub1 = REVERSE(SUBSTRING(@reverse,CHARINDEX('-',@reverse,0)+1,LEN(@temp)-CHARINDEX('-',@reverse,0)))

select @sub1, @sub2, @sub3

Answer 2

There has been a lot written on efficient splitting functions for TSQL. I suspect that diving into that is probably overkill for this question as it is very specific.

Here's something that demostrate some basic TSQL string manipulation.

NOTE: I use a common table expression (CTE) for clarity, but those manipulations could be done in line.

DECLARE @build_names VARCHAR(5000) = NULL

SET @build_names = 'BB10-1_X-4759-566549'

;WITH cte AS (
 SELECT CHARINDEX('-',REVERSE(@build_names),1) RevPosLastSep
       ,CHARINDEX('-',@build_names,CHARINDEX('-',@build_names,1)+1) PosFirstSep
)
SELECT RIGHT(@build_names,RevPosLastSep-1)
      ,SUBSTRING(@build_names,PosFirstSep+1,LEN(@build_names) - RevPosLastSep - PosFirstSep)
      ,LEFT(@build_names,PosFirstSep-1)
  FROM cte