Parsing CSV or other delimited strings in SQL Server
In this script we will look at various ways we can design a string split function in SQL Server to split a delimited string in to individual rows.
We will use the following csv delimited string for building our split function.
DECLARE @csv_str VARCHAR(8000)
SET @csv_str = 'cat,dog,rat,ant,tiger'
Method 1 : USING charindex function
In this method we will use the system string function charindex. We loop through the string looking for the delimiter and parse the string accordingly.
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SplitString_Using_Charindex]')
AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[SplitString_Using_Charindex]
GO
CREATE FUNCTION SplitString_Using_Charindex (@csv_str VARCHAR(8000),@delimiter varchar(20) )
RETURNS @splittable TABLE (id int identity(1,1), csvvalues VARCHAR(256) )
AS
BEGIN
DECLARE @pointer int
-- Check for NULL string or empty sting
IF (LEN(@csv_str) < 1 OR @csv_str IS NULL)
BEGIN
RETURN
END
-- Loop through the string
WHILE (LEN(@csv_str) > 0)
BEGIN
SELECT @pointer = CHARINDEX(@delimiter,@csv_str)
--IF @pointer > 0 then we found the delimiter in the string
IF (@pointer > 0)
BEGIN
INSERT INTO @splittable(csvvalues)
SELECT LEFT(@csv_str,@pointer-1)
SET @csv_str = RIGHT(@csv_str,LEN(@csv_str) - @pointer)
END
ELSE -- Didnot find the delimiter. Hence full string is one value. Insert it into output table
BEGIN
INSERT INTO @splittable(csvvalues)
SELECT @csv_str
SET @csv_str = ''
END
END
RETURN
END
GO
Sample Run
DECLARE @csv_str VARCHAR(8000)
,@delimiter VARCHAR(20)
SET @csv_str = 'cat,dog,rat,ant,tiger'
SET @delimiter =','
SELECT * FROM dbo.SplitString_Using_Charindex(@csv_str,@delimiter)
Method 2: Using Charindex and CTE
In this method we use the combination of CTE and Charindex.
Note this method will work for SQL Server 2005 onwards.
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].
[SplitString_Using_CTE_Charindex]')
AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[SplitString_Using_CTE_Charindex]
GO
CREATE FUNCTION SplitString_Using_CTE_Charindex (@csv_str VARCHAR(8000),
@delimiter varchar(20) )
RETURNS @splittable TABLE (id int identity(1,1), csvvalues VARCHAR(256) )
AS
BEGIN
-- Check for NULL string or empty sting
IF (LEN(@csv_str) < 1 OR @csv_str IS NULL)
BEGIN
RETURN
END
; WITH csvtbl(i,j)
AS
(
SELECT i=1, j= CHARINDEX(@delimiter,@csv_str+@delimiter)
UNION ALL
SELECT i=j+1, j=CHARINDEX(@delimiter,@csv_str+@delimiter,j+1)
FROM csvtbl
WHERE CHARINDEX(@delimiter,@csv_str+@delimiter,j+1) <> 0
)
INSERT INTO @splittable ( csvvalues)
SELECT SUBSTRING(@csv_str,i,j-i)
FROM csvtbl
RETURN
END
GO
Sample Run:
DECLARE @csv_str VARCHAR(8000)
,@delimiter VARCHAR(20)
SET @csv_str = 'cat#dog#rat#ant#tiger'
SET @delimiter ='#'
SELECT * FROM dbo.SplitString_Using_CTE_Charindex(@csv_str,@delimiter)
GO
Method 3: Using XML
Here we will first convert the csv into XML and then using XML functions to parse the string.
Note this method can be used on SQL Server 2005 onwards.
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SplitString_Using_XML]')
AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[SplitString_Using_XML]
GO
CREATE FUNCTION SplitString_Using_XML (@csv_str VARCHAR(8000),@delimiter varchar(20) )
RETURNS @splittable TABLE (id int identity(1,1), csvvalues VARCHAR(256) )
AS
BEGIN
-- Check for NULL string or empty sting
IF (LEN(@csv_str) < 1 OR @csv_str IS NULL)
BEGIN
RETURN
END
--Convert the input csv into XML
DECLARE @csv_xml XML
SELECT @csv_xml = '<str>'+REPLACE(@csv_str,@delimiter,'</str><str>')+'</str>'
INSERT INTO @splittable(csvvalues)
SELECT csvvalues.value('.', 'VARCHAR(256)') AS value
FROM @csv_xml.nodes('//str') tbl(csvvalues)
RETURN
END
GO
Sample Run
DECLARE @csv_str VARCHAR(8000)
,@delimiter VARCHAR(20)
SET @csv_str = 'cat@#dog@#rat@#ant@#tiger'
SET @delimiter ='@#'
SELECT * FROM dbo.SplitString_Using_XML(@csv_str,@delimiter)
GO
