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 :)

Answer 2

Try this (assuming SQL Server 2005+)

DECLARE @t TABLE(ColumnValue VARCHAR(50))
INSERT INTO @t(ColumnValue) SELECT 'josh,Reg01,False,a0-t0,22/09/2010'
INSERT INTO @t(ColumnValue) SELECT 'mango,apple,bannana,grapes'
INSERT INTO @t(ColumnValue) SELECT 'stackoverflow'

SELECT ThirdValue = splitdata
FROM(
        SELECT 
            Rn = ROW_NUMBER() OVER(PARTITION BY ColumnValue ORDER BY (SELECT 1))
            ,X.ColumnValue
            ,Y.splitdata 
        FROM
         (
            SELECT *,
            CAST('<X>'+REPLACE(F.ColumnValue,',','</X><X>')+'</X>' AS XML) AS xmlfilter FROM @t F
         )X
         CROSS APPLY
         ( 
            SELECT fdata.D.value('.','varchar(50)') AS splitdata 
            FROM X.xmlfilter.nodes('X') as fdata(D)
         ) Y
    )X WHERE X.Rn = 3

//Result

ThirdValue

False
bannana

Also it is not very clear from your question as what version of SQL Server you are using. In case you are using SQL SERVER 2000, you can go ahead with the below approach.

Step 1: Create a number table

CREATE TABLE dbo.Numbers
(
   N INT NOT NULL PRIMARY KEY
);
GO

DECLARE @rows AS INT;
SET @rows = 1;

INSERT INTO dbo.Numbers VALUES(1);
WHILE(@rows <= 10000)
BEGIN
   INSERT INTO dbo.Numbers SELECT N + @rows FROM dbo.Numbers;
   SET @rows = @rows * 2;
END 

Step 2: Apply the query below

DECLARE @t TABLE(ColumnValue VARCHAR(50))
INSERT INTO @t(ColumnValue) SELECT 'josh,Reg01,False,a0-t0,22/09/2010'
INSERT INTO @t(ColumnValue) SELECT 'mango,apple,bannana,grapes'
INSERT INTO @t(ColumnValue) SELECT 'stackoverflow'

--Declare a table variable to put the identity column and store the indermediate results
DECLARE @tempT TABLE(Id INT IDENTITY,ColumnValue VARCHAR(50),SplitData VARCHAR(50))

-- Insert the records into the table variable
INSERT INTO @tempT
SELECT  
    ColumnValue
    ,SUBSTRING(ColumnValue, Numbers.N,CHARINDEX(',', ColumnValue + ',', Numbers.N) - Numbers.N) AS splitdata 
FROM @t 
JOIN Numbers ON Numbers.N <= DATALENGTH(ColumnValue) + 1  
AND SUBSTRING(',' + ColumnValue, Numbers.N, 1) = ','  

--Project the filtered records

SELECT ThirdValue = X.splitdata
FROM
--The co-related subquery does the ROW_NUMBER() OVER(PARTITION BY ColumnValue)
(SELECT 
  Rn = (SELECT COUNT(*) 
        FROM @tempT t2 
        WHERE t2.ColumnValue=t1.ColumnValue 
        AND t2.Id<=t1.Id)
 ,t1.ColumnValue
 ,t1.splitdata
FROM @tempT t1)X
WHERE X.Rn =3

-- Result

ThirdValue

False
bannana

Also you can use Master..spt_Values for your number table

DECLARE @t TABLE(ColumnValue VARCHAR(50))
INSERT INTO @t(ColumnValue) SELECT 'josh,Reg01,False,a0-t0,22/09/2010'
INSERT INTO @t(ColumnValue) SELECT 'mango,apple,bannana,grapes'
INSERT INTO @t(ColumnValue) SELECT 'stackoverflow'

--Declare a table variable to put the identity column and store the indermediate results
DECLARE @tempT TABLE(Id INT IDENTITY,ColumnValue VARCHAR(50),SplitData VARCHAR(50))

-- Insert the records into the table variable
INSERT INTO @tempT
SELECT  
    ColumnValue
    ,SUBSTRING(ColumnValue, Number ,CHARINDEX(',', ColumnValue + ',', Number ) - Number) AS splitdata 
FROM @t 
JOIN master..spt_values ON Number <= DATALENGTH(ColumnValue) + 1  AND type='P'
AND SUBSTRING(',' + ColumnValue, Number , 1) = ','  

--Project the filtered records
SELECT ThirdValue = X.splitdata
FROM
--The co-related subquery does the ROW_NUMBER() OVER(PARTITION BY ColumnValue)
(SELECT 
  Rn = (SELECT COUNT(*) 
        FROM @tempT t2 
        WHERE t2.ColumnValue=t1.ColumnValue 
        AND t2.Id<=t1.Id)
 ,t1.ColumnValue
 ,t1.splitdata
FROM @tempT t1)X
WHERE X.Rn =3

You can read about this from

1) What is the purpose of system table table master..spt_values and what are the meanings of its values?

2) Why (and how) to split column using master..spt_values?

Answer 3

You really need something like String.Split(',')(2) which unfortunately dos not exist in SQL but this may be helpful to you

Answer 4

You can make some test with this solution and the other ones but, I believe that using XML in such situations almost always gives to you best performance and insure less coding:

DECLARE @InPutCSV NVARCHAR(2000)= 'josh,Reg01,False,a0-t0,22/09/2010'
DECLARE @ValueIndexToGet INT=3
DECLARE @XML XML =  CAST ('<d>' + REPLACE(@InPutCSV, ',', '</d><d>') + '</d>' AS XML);

WITH CTE(RecordNumber,Value) AS
(
     SELECT  ROW_NUMBER() OVER(ORDER BY T.v.value('.', 'NVARCHAR(100)') DESC) AS RecordNumber
             ,T.v.value('.', 'NVARCHAR(100)') AS Value
     FROM @XML.nodes('/d') AS T(v)
)
SELECT Value
FROM CTE WHERE RecordNumber=@ValueIndexToGet

I can confirm that it takes 1 seconds to get value from CSV string with 100 000 values.