t-sql find specific value with a csv string

Question

I need some on help a SQL Query. I have a column with values stored as comma separated values.

I need to write a query which finds the 3rd delimited item within each value in the column.

Is this possible to do this in a Select statement? ex: ColumnValue: josh,Reg01,False,a0-t0,22/09/2010

So I will need to get the 3rd value (i.e.) False from the above string.

Answer 1

Yes.

Where @s is your string...

select 
    SUBSTRING (@s,
    CHARINDEX(',',@s,CHARINDEX(',',@s)+1)+1,
    CHARINDEX(',',@s,CHARINDEX(',',@s,CHARINDEX(',',@s)+1)+1)
          -CHARINDEX(',',@s,CHARINDEX(',',@s)+1)-1)

Or more generically...

;with cte as 
(
    select 1 as Item, 1 as Start, CHARINDEX(',',@s, 1) as Split
    union all
    select cte.Item+1, cte.Split+1, nullif(CHARINDEX(',',@s, cte.Split+1),0) as Split
    from cte
    where cte.Split<>0  
)   
select SUBSTRING(@s, start,isnull(split,len(@s)+1)-start) 
from cte 
where Item = 3

Now store your data properly :)