Retrieve a string between 2 same variables SQL

Question

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 -.

Answer 1

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)