SQL server - parsing element of delemiated string

Question

I have delimited string like this 'test1:test2:test3:test4:test5'.

I need to get the third and forth values one value at a time. The string always has five values.

Answer 1

You can take a SUBSTRING of your string to ignore the first element, then use PARSENAME. Then, you don't have to use a function.

declare @var varchar(64) = 'test1:test2:test3:test4:test5'

select 
    ThirdElement = parsename(replace(substring(@var,charindex(':',@var) + 1,len(@var)),':','.'),3)
    ,FourthElement = parsename(replace(substring(@var,charindex(':',@var) + 1,len(@var)),':','.'),2)