T-SQL split string

I have developed a double Splitter (Takes two split characters) as requested [Here][1]. Could be of some value in this thread seeing its the most referenced for queries relating to string splitting.

CREATE FUNCTION uft_DoubleSplitter
(
-- Add the parameters for the function here
@String VARCHAR(4000),
@Splitter1 CHAR,
@Splitter2 CHAR
)
RETURNS @Result TABLE (Id INT,MId INT,SValue VARCHAR(4000))
AS
BEGIN
DECLARE @FResult TABLE(Id INT IDENTITY(1, 1),
SValue VARCHAR(4000))
DECLARE @SResult TABLE(Id INT IDENTITY(1, 1),
MId INT,
SValue VARCHAR(4000))
SET @String = @String+@Splitter1

WHILE CHARINDEX(@Splitter1, @String) > 0
BEGIN
DECLARE @WorkingString VARCHAR(4000) = NULL

SET @WorkingString = SUBSTRING(@String, 1, CHARINDEX(@Splitter1, @String) - 1)
--Print @workingString

INSERT INTO @FResult
SELECT CASE
WHEN @WorkingString = '' THEN NULL
ELSE @WorkingString
END

SET @String = SUBSTRING(@String, LEN(@WorkingString) + 2, LEN(@String))

END
IF ISNULL(@Splitter2, '') != ''
BEGIN
DECLARE @OStartLoop INT
DECLARE @OEndLoop INT

SELECT @OStartLoop = MIN(Id),
@OEndLoop = MAX(Id)
FROM @FResult

WHILE @OStartLoop <= @OEndLoop
BEGIN
DECLARE @iString VARCHAR(4000)
DECLARE @iMId INT

SELECT @iString = SValue+@Splitter2,
@iMId = Id
FROM @FResult
WHERE Id = @OStartLoop

WHILE CHARINDEX(@Splitter2, @iString) > 0
BEGIN
DECLARE @iWorkingString VARCHAR(4000) = NULL

SET @IWorkingString = SUBSTRING(@iString, 1, CHARINDEX(@Splitter2, @iString) - 1)

INSERT INTO @SResult
SELECT @iMId,
CASE
WHEN @iWorkingString = '' THEN NULL
ELSE @iWorkingString
END

SET @iString = SUBSTRING(@iString, LEN(@iWorkingString) + 2, LEN(@iString))

END

SET @OStartLoop = @OStartLoop + 1
END
INSERT INTO @Result
SELECT MId AS PrimarySplitID,
ROW_NUMBER() OVER (PARTITION BY MId ORDER BY Mid, Id) AS SecondarySplitID ,
SValue
FROM @SResult
END
ELSE
BEGIN
INSERT INTO @Result
SELECT Id AS PrimarySplitID,
NULL AS SecondarySplitID,
SValue
FROM @FResult
END
RETURN

**Usage:**

--FirstSplit
SELECT * FROM uft_DoubleSplitter('ValueA=ValueB=ValueC=ValueD==ValueE&ValueA=ValueB=ValueC===ValueE&ValueA=ValueB==ValueD===','&',NULL)

--Second Split
SELECT * FROM uft_DoubleSplitter('ValueA=ValueB=ValueC=ValueD==ValueE&ValueA=ValueB=ValueC===ValueE&ValueA=ValueB==ValueD===','&','=')

**Possible Usage (Get second value of each split):**

SELECT fn.SValue
FROM uft_DoubleSplitter('ValueA=ValueB=ValueC=ValueD==ValueE&ValueA=ValueB=ValueC===ValueE&ValueA=ValueB==ValueD===', '&', '=')AS fn
WHERE fn.mid = 2


[1]:

[To see links please register here]

The easiest way to do this is by using `XML` format.

**1. Converting string to rows without table**

**QUERY**

DECLARE @String varchar(100) = 'String1,String2,String3'
-- To change ',' to any other delimeter, just change ',' to your desired one
DECLARE @Delimiter CHAR = ','

SELECT LTRIM(RTRIM(Split.a.value('.', 'VARCHAR(100)'))) 'Value'
FROM
(
SELECT CAST ('<M>' + REPLACE(@String, @Delimiter, '</M><M>') + '</M>' AS XML) AS Data
) AS A
CROSS APPLY Data.nodes ('/M') AS Split(a)

**RESULT**

x---------x
| Value |
x---------x
| String1 |
| String2 |
| String3 |
x---------x

**2. Converting to rows from a table which have an ID for each CSV row**

**SOURCE TABLE**

x-----x--------------------------x
| Id | Value |
x-----x--------------------------x
| 1 | String1,String2,String3 |
| 2 | String4,String5,String6 |
x-----x--------------------------x

**QUERY**

-- To change ',' to any other delimeter, just change ',' before '</M><M>' to your desired one
DECLARE @Delimiter CHAR = ','

SELECT ID,LTRIM(RTRIM(Split.a.value('.', 'VARCHAR(100)'))) 'Value'
FROM
(
SELECT ID,CAST ('<M>' + REPLACE(VALUE, @Delimiter, '</M><M>') + '</M>' AS XML) AS Data
FROM TABLENAME
) AS A
CROSS APPLY Data.nodes ('/M') AS Split(a)

**RESULT**

x-----x----------x
| Id | Value |
x-----x----------x
| 1 | String1 |
| 1 | String2 |
| 1 | String3 |
| 2 | String4 |
| 2 | String5 |
| 2 | String6 |
x-----x----------x

There is a correct version on here but I thought it would be nice to add a little fault tolerance in case they have a trailing comma as well as make it so you could use it not as a function but as part of a larger piece of code. Just in case you're only using it once time and don't need a function. This is also for integers (which is what I needed it for) so you might have to change your data types.

DECLARE @StringToSeperate VARCHAR(10)
SET @StringToSeperate = '1,2,5'

--SELECT @StringToSeperate IDs INTO #Test

DROP TABLE #IDs
CREATE TABLE #IDs (ID int)

DECLARE @CommaSeperatedValue NVARCHAR(255) = ''
DECLARE @Position INT = LEN(@StringToSeperate)

--Add Each Value
WHILE CHARINDEX(',', @StringToSeperate) > 0
BEGIN
SELECT @Position = CHARINDEX(',', @StringToSeperate)
SELECT @CommaSeperatedValue = SUBSTRING(@StringToSeperate, 1, @Position-1)

INSERT INTO #IDs
SELECT @CommaSeperatedValue

SELECT @StringToSeperate = SUBSTRING(@StringToSeperate, @Position+1, LEN(@StringToSeperate)-@Position)

END

--Add Last Value
IF (LEN(LTRIM(RTRIM(@StringToSeperate)))>0)
BEGIN
INSERT INTO #IDs
SELECT SUBSTRING(@StringToSeperate, 1, @Position)
END

SELECT * FROM #IDs

I modified +Andy Robinson's function a little bit. Now you can select only required part from returning table:




CREATE FUNCTION dbo.splitstring ( @stringToSplit VARCHAR(MAX) )

RETURNS

@returnList TABLE ([numOrder] [tinyint] , [Name] [nvarchar] (500)) AS
BEGIN

DECLARE @name NVARCHAR(255)

DECLARE @pos INT

DECLARE @orderNum INT

SET @orderNum=0

WHILE CHARINDEX('.', @stringToSplit) > 0

BEGIN
SELECT @orderNum=@orderNum+1;
SELECT @pos = CHARINDEX('.', @stringToSplit)
SELECT @name = SUBSTRING(@stringToSplit, 1, @pos-1)

INSERT INTO @returnList
SELECT @orderNum,@name

SELECT @stringToSplit = SUBSTRING(@stringToSplit, @pos+1, LEN(@stringToSplit)-@pos)
END
SELECT @orderNum=@orderNum+1;
INSERT INTO @returnList
SELECT @orderNum, @stringToSplit

RETURN
END


Usage:

<code>SELECT Name FROM dbo.splitstring('ELIS.YD.CRP1.1.CBA.MDSP.T389.BT') WHERE numOrder=5
</code>

You can Use this function:

CREATE FUNCTION SplitString
(
@Input NVARCHAR(MAX),
@Character CHAR(1)
)
RETURNS @Output TABLE (
Item NVARCHAR(1000)
)
AS
BEGIN

DECLARE @StartIndex INT, @EndIndex INT
SET @StartIndex = 1
IF SUBSTRING(@Input, LEN(@Input) - 1, LEN(@Input)) <> @Character
BEGIN
SET @Input = @Input + @Character
END

WHILE CHARINDEX(@Character, @Input) > 0
BEGIN
SET @EndIndex = CHARINDEX(@Character, @Input)

INSERT INTO @Output(Item)
SELECT SUBSTRING(@Input, @StartIndex, @EndIndex - 1)

SET @Input = SUBSTRING(@Input, @EndIndex + 1, LEN(@Input))
END

RETURN
END
GO

The easiest way:

1. Install SQL Server 2016
2. Use STRING_SPLIT

[To see links please register here]

It works even in express edition :).

Finally the wait is over in **SQL Server 2016** they have introduced Split string function : [**`STRING_SPLIT`**][1]

select * From STRING_SPLIT ('a,b', ',') cs

All the other methods to split string like XML, Tally table, while loop, etc.. has been blown away by this `STRING_SPLIT` function.

Here is an excellent article with performance comparison : [**Performance Surprises and Assumptions : STRING_SPLIT**][2]


[1]:

[To see links please register here]

[2]:

[To see links please register here]

**Here is an example that you can use as function or also you can put the same logic in procedure.
--SELECT * from [dbo].[fn_SplitString]('red,blue,green',',') ;**


CREATE FUNCTION [dbo].[fn_SplitString]
(@CSV VARCHAR(MAX), @Delimeter VARCHAR(100) = ',')
RETURNS @retTable TABLE
(

[value] VARCHAR(MAX) NULL
)AS

BEGIN

DECLARE
@vCSV VARCHAR (MAX) = @CSV,
@vDelimeter VARCHAR (100) = @Delimeter;

IF @vDelimeter = ';'
BEGIN
SET @vCSV = REPLACE(@vCSV, ';', '~!~#~');
SET @vDelimeter = REPLACE(@vDelimeter, ';', '~!~#~');
END;

SET @vCSV = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@vCSV, '&', '&'), '<', '<'), '>', '>'), '''', '''), '"', '"');

DECLARE @xml XML;

SET @xml = '<i>' + REPLACE(@vCSV, @vDelimeter, '</i><i>') + '</i>';

INSERT INTO @retTable
SELECT
x.i.value('.', 'varchar(max)') AS COLUMNNAME
FROM @xml.nodes('//i')AS x(i);

RETURN;
END;

All the functions for string splitting that use some kind of Loop-ing (iterations) have bad performance. They should be replaced with set-based solution.

This code executes excellent.

CREATE FUNCTION dbo.SplitStrings
(
@List NVARCHAR(MAX),
@Delimiter NVARCHAR(255)
)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
(
SELECT Item = y.i.value('(./text())[1]', 'nvarchar(4000)')
FROM
(
SELECT x = CONVERT(XML, '<i>'
+ REPLACE(@List, @Delimiter, '</i><i>')
+ '</i>').query('.')
) AS a CROSS APPLY x.nodes('i') AS y(i)
);
GO

The often used approach with XML elements breaks in case of forbidden characters. This is an approach to use this method with any kind of character, even with the semicolon as delimiter.

The trick is, first to use `SELECT SomeString AS [*] FOR XML PATH('')` to get all forbidden characters properly escaped. That's the reason, why I replace the delimiter to a *magic value* to avoid troubles with `;` as delimiter.

DECLARE @Dummy TABLE (ID INT, SomeTextToSplit NVARCHAR(MAX))
INSERT INTO @Dummy VALUES
(1,N'A&B;C;D;E, F')
,(2,N'"C" & ''D'';<C>;D;E, F');

DECLARE @Delimiter NVARCHAR(10)=';'; --special effort needed (due to entities coding with "&code;")!

WITH Casted AS
(
SELECT *
,CAST(N'<x>' + REPLACE((SELECT REPLACE(SomeTextToSplit,@Delimiter,N'§§Split$me$here§§') AS [*] FOR XML PATH('')),N'§§Split$me$here§§',N'</x><x>') + N'</x>' AS XML) AS SplitMe
FROM @Dummy
)
SELECT Casted.ID
,x.value(N'.',N'nvarchar(max)') AS Part
FROM Casted
CROSS APPLY SplitMe.nodes(N'/x') AS A(x)

The result

ID Part
1 A&B
1 C
1 D
1 E, F
2 "C" & 'D'
2 <C>
2 D
2 E, F