I have a column in Table1 with string in it separated by commma:
Id Val
1 ,4
2 ,3,1,0
3 NULL
4 ,5,2
Is there a simple way to split and get any value from that column, for example
SELECT Value(1) FROM Table1 should get
Id Val
1 4
2 3
3 NULL
4 5
SELECT Value(2) FROM Table1 should get
Id Val
1 NULL
2 1
3 NULL
4 2
Thank you!
Storing comma separated values in a column is always a pain, consider changing your table structure
To get this done, create a split string function. Here is one of the best possible approach to split the string to individual rows. Referred from http://www.sqlservercentral.com/articles/Tally+Table/72993/
CREATE FUNCTION [dbo].[DelimitedSplit8K]
(@pString VARCHAR(8000), @pDelimiter CHAR(1))
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
--===== "Inline" CTE Driven "Tally Table" produces values from 0 up to 10,000...
-- enough to cover NVARCHAR(4000)
WITH E1(N) AS (
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
), --10E+1 or 10 rows
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
cteTally(N) AS (--==== This provides the "base" CTE and limits the number of rows right up front
-- for both a performance gain and prevention of accidental "overruns"
SELECT TOP (ISNULL(DATALENGTH(@pString),0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
),
cteStart(N1) AS (--==== This returns N+1 (starting position of each "element" just once for each delimiter)
SELECT 1 UNION ALL
SELECT t.N+1 FROM cteTally t WHERE SUBSTRING(@pString,t.N,1) = @pDelimiter
),
cteLen(N1,L1) AS(--==== Return start and length (for use in substring)
SELECT s.N1,
ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,s.N1),0)-s.N1,8000)
FROM cteStart s
)
--===== Do the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found.
SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY l.N1),
Item = SUBSTRING(@pString, l.N1, l.L1)
FROM cteLen l
to call the function
SELECT *
FROM yourtable
CROSS apply (SELECT CASE WHEN LEFT(val, 1) = ',' THEN Stuff(val, 1, 1, '') ELSE val END) cs (cleanedval)
CROSS apply [dbo].[Delimitedsplit8k](cs.cleanedval, ',')
WHERE ItemNumber = 1
SELECT *
FROM yourtable
CROSS apply (SELECT CASE WHEN LEFT(val, 1) = ',' THEN Stuff(val, 1, 1, '') ELSE val END) cs (cleanedval)
CROSS apply [dbo].[Delimitedsplit8k](cs.cleanedval, ',')
WHERE ItemNumber = 2
Another option using a Parse/Split Function and an OUTER APPLY
Example
Declare @YourTable Table ([Id] int,[Val] varchar(50))
Insert Into @YourTable Values
(1,',4')
,(2,',3,1,0')
,(3,NULL)
,(4,',5,2')
Select A.ID
,Val = B.RetVal
From @YourTable A
Outer Apply (
Select * From [dbo].[tvf-Str-Parse](A.Val,',')
Where RetSeq = 2
) B
Returns
ID Val
1 4
2 3
3 NULL
4 5
The UDF if Interested
CREATE FUNCTION [dbo].[tvf-Str-Parse] (@String varchar(max),@Delimiter varchar(10))
Returns Table
As
Return (
Select RetSeq = Row_Number() over (Order By (Select null))
,RetVal = LTrim(RTrim(B.i.value('(./text())[1]', 'varchar(max)')))
From (Select x = Cast('<x>' + replace((Select replace(@String,@Delimiter,'§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml).query('.')) as A
Cross Apply x.nodes('x') AS B(i)
);
Try This Logic Using recursive CTE
DECLARE @Pos INT = 2
DECLARE @T TABLE
(
Id INT,
Val VARCHAR(50)
)
INSERT INTO @T
VALUES(1,',4'),(2,',3,1,0'),(3,NULL),(4,',5,2')
;WITH CTE
AS
(
SELECT
Id,
SeqNo = 0,
MyStr = SUBSTRING(Val,CHARINDEX(',',Val)+1,LEN(Val)),
Num = REPLACE(SUBSTRING(Val,1,CHARINDEX(',',Val)),',','')
FROM @T
UNION ALL
SELECT
Id,
SeqNo = SeqNo+1,
MyStr = CASE WHEN CHARINDEX(',',MyStr)>0
THEN SUBSTRING(MyStr,CHARINDEX(',',MyStr)+1,LEN(MyStr))
ELSE NULL END,
Num = CASE WHEN CHARINDEX(',',MyStr)>0
THEN REPLACE(SUBSTRING(MyStr,1,CHARINDEX(',',MyStr)),',','')
ELSE MyStr END
FROM CTE
WHERE ISNULL(REPLACE(MyStr,',',''),'')<>''
)
SELECT
T.Id,
CTE.Num
FROM @T t
LEFT JOIN CTE
ON T.Id = cte.Id
AND SeqNo = @Pos
My Output for the above
Declare @t TABLE (Id INT , Val VARCHAR(100))
INSERT INTO @t VALUES
(1 , '4'),
(2 , '3,1,0'),
(3 , NULL),
(4 , '5,2')
CREATE FUNCTION [dbo].[fn_xml_Splitter]
(
@delimited nvarchar(max)
, @delimiter nvarchar(1)
, @Position INT = NULL
)
RETURNS TABLE
AS
RETURN
(
SELECT Item
FROM (
SELECT Split.a.value('.', 'VARCHAR(100)') Item
, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) ItemNumber
FROM
(SELECT Cast ('<X>' + Replace(@delimited, @delimiter, '</X><X>')
+ '</X>' AS XML) AS Data
) AS t CROSS APPLY Data.nodes ('/X') AS Split(a)
)x
WHERE x.ItemNumber = @Position OR @Position IS NULL
);
GO
Now you can call this function in two different ways.
1 . to get return an Item on a specific position, specify the position in the 3rd parameter of the function:
SELECT *
FROM @t t
CROSS APPLY [dbo].[fn_xml_Splitter](t.Val , ',', 1)
2 . to get return all items, specify the key word DEFUALT in the 3rd parameter of the function:
SELECT *
FROM @t t
CROSS APPLY [dbo].[fn_xml_Splitter](t.Val , ',', DEFAULT)
Here is an example of using a CTE combined with converting the CSV to XML:
DECLARE @Test TABLE (
CsvData VARCHAR(10)
);
INSERT INTO @Test (CsvData)
VALUES
('1,2,3'),
(',4,5,7'),
(NULL),
(',3,');
WITH XmlData AS (
SELECT CONVERT(XML, '<val>' + REPLACE(CsvData, ',', '</val><val>') + '</val>') [CsvXml]
FROM @Test
)
SELECT xd.CsvXml.value('val[2]', 'VARCHAR(10)')
FROM XmlData xd;
This would output:
2
4
NULL
3
The column to display is controlled by the XPath query. In this case, val[2].
The main advantage here is that no user-defined functions are required.
