I have a string something like this 1234-56-7-8-9012
. This can be any size and the values between the -
can be any size. I need to extract the 7
in the middle of this string but cannot accommodate to the changing size of the string. This is what I've been using but it doesn't take the change of size:
@String = '1234-56-7-8-9012'
SUBSTRING(
SUBSTRING(@String,CHARINDEX('-',@String)+1,LEN(@String))
,CHARINDEX('-'
,SUBSTRING(@String,CHARINDEX('-',@String)+1,LEN(@String))
)+1
,1
)
This will give me the 7 value, but sometimes that value will be 2 in length. Basically I need to get the number between the 2nd and 3rd -
.
Try this:
declare @String varchar(50) = '1234-56-7-8-9012'
select substring(@String, charindex('-',@String,charindex('-',@String,1)+1)+1,
charindex('-',@String,charindex('-',@String,charindex('-',@String,1)+1)+1)-
charindex('-',@String,charindex('-',@String,1)+1)-1)