T-sql find specific value using csv string

Question

I need to help SQL Query a bit. I have a column with values ​​stored as comma separated values.

I need to write a query that will find the third separator element in each value in the column.

Can this be done in the Select statement? ex: ColumnValue: josh,Reg01,False,a0-t0,22/09/2010

So I will need to get the third value (i.e.) False from the above line.

Answer 1

Yes.

Where @s is your line ...

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

 ;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 save your data correctly :)

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 clear from your question which version of SQL Server you are using. If you are using SQL SERVER 2000, you can use the approach below.

Step 1. Create a table of numbers

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

You can also 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 it from

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

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

Answer 3

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

Answer 4

You can do some tests with this solution and others, but I believe that using XML in such situations almost always gives you better performance and provides 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 Tvvalue('.', 'NVARCHAR(100)') DESC) AS RecordNumber ,Tvvalue('.', '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 second to get a value from a CSV string with 100,000 values.