In previous post I mentioned different ways to get comma separated values (CSV) from a table column.
Following are the different ways to convert comma separated string to column:
-- -- Using while loop:
DECLARE @strCSVString VARCHAR(4000)
SELECT @strCSVString = '1,2,3,4,5'
DECLARE @Result TABLE(ColumnValue NVARCHAR(100))
DECLARE @str VARCHAR(20)
DECLARE @idx Int
IF ISNULL(@strCSVString,'') <> ''
BEGIN
SET @idx = CHARINDEX(',' , @strCSVString)
WHILE @idx > 0
BEGIN
SET @str = SUBSTRING(@strCSVString, 1, @idx - 1)
SET @strCSVString = SUBSTRING(@strCSVString, @idx + 1, LEN(@strCSVString) - @idx)
INSERT INTO @Result values (@str)
SET @idx = CHARINDEX(',' , @strCSVString)
END
SET @str = @strCSVString
INSERT INTO @Result values (@str)
END
SELECT * FROM @Result
GO
-- -- Using Common Table Expression (CTE):
DECLARE @strCSVString VARCHAR(4000)
SELECT @strCSVString = '1,2,3,4,5'
DECLARE @Result TABLE(ColumnValue NVARCHAR(100))
; WITH CTE(Start, [Stop]) AS
(
SELECT 1, CHARINDEX(',' , @strCSVString )
UNION ALL
SELECT [Stop] + 1, CHARINDEX(',' ,@strCSVString , [Stop] + 1)
FROM CTE
WHERE [Stop] > 0
)
INSERT INTO @Result
SELECT SUBSTRING(@strCSVString , Start, CASE WHEN stop > 0 THEN [Stop]-Start ELSE 4000 END) AS stringValue
FROM CTE
SELECT * FROM @Result
GO
-- -- Using XML:
DECLARE @strCSVString VARCHAR(4000)
SELECT @strCSVString = '1,2,3,4,5'
DECLARE @Result TABLE(ColumnValue NVARCHAR(100))
DECLARE @xmlData XML
SELECT @xmlData = CAST('<Col>'+ REPLACE(@strCSVString,',','</Col><Col>')+ '</Col>' AS XML)
INSERT INTO @Result
SELECT d.value('.', 'int') AS Val
FROM @xmlData.nodes('/Col') AS x(d)
SELECT * FROM @Result
GO
We can use the above code by creating a function and can change the delimiter string from comma to any valid character by having a parameter in function.
For example, let's create a funtion, dbo.Split() that converts comma delimited string in to a table column.
CREATE FUNCTION [dbo].[Split]
(
@strCSVString nvarchar(2000),
@Delimiter nvarchar(5)
)
RETURNS @Result TABLE (ColumnValue NVARCHAR(100))
AS
BEGIN
;WITH CTE(Start, [Stop]) AS
(
SELECT 1, CHARINDEX(@Delimiter , @strCSVString )
UNION ALL
SELECT [Stop] + 1, CHARINDEX(@Delimiter, @strCSVString, [Stop] + 1)
FROM CTE
WHERE [Stop] > 0
)
INSERT INTO @Result
SELECT SUBSTRING(@strCSVString , Start, CASE WHEN stop > 0 THEN [Stop] - Start ELSE 4000 END) AS StringValue
FROM CTE
RETURN
END
GO
-----------------
SELECT * FROM dbo.[Split]('1,2,3,4,5' , ',')
SELECT * FROM dbo.[Split]('1|2|3|4|5' , '|')
-----------------
Output:
The all above queries will give the same result as:
Thanks Bro :)
ReplyDelete