SQL Script to split string using SQL Server

NOTE: In 2016 version of SQL Server, a new function STRING_SPLIT (GivenString, Separator) has been implemented but for below 2016 version has no any string function like this one.

So that, I am sharing a user defined table valued function, named SplitString(GivenString, Separator, ReturnSeperator), by which you can Split any string by using any separator. Also, this function has another parameter by which you can return the separator also.

See below parameter description

SplitString(givenString , separator, returnSeperator) -- Download the sql script or see below for the coding

Returns a Table with two columns – OrdinalPosition & Value

Param Name	Type	Direction	Description
givenString	VARCHAR (8000)	IN	A String, which one you want to parse/split
separator	VARCHAR (8000)	IN	A String, which is used as a separator for concatenated strings.
returnSeperator	BIT	IN	Default is False. For True/1 value, return String will have the separator at the end For False/0, No separator will be returned.

Return Column	Type	Description
OrdinalPosition	INT	This column will represent the position of the value string in GivenString
Value	VARCHAR	String value as varchar(max length of value) which has been split from GivenString

Uses:

Select * from SplitString (‘abc,xyz,pqr’ , ‘,’, False) Select * from SplitString (‘abc,xyz,pqr’ , ‘,’, default)	OrdinalPosition Value 1 abc 2 xyz 3 pqr
Select * from SplitString (‘abc,xyz,pqr’ , ‘,’, true)	OrdinalPosition Value 1 abc, 2 xyz, 3 pqr,

Here is the Table Valued Function script

SplitString: download as file

-----------------------------------------------------------------------

CREATE FUNCTION [dbo].[SplitString] (   @givenString varchar(8000) , @separator varchar(100) , @returnSeparator bit = 0) RETURNS TABLE AS   RETURN (   WITH data ([start], [end])   AS   (       SELECT         0 AS [start],         CHARINDEX(@separator, @givenString) AS [end]          UNION ALL          SELECT         [end] + 1,         CHARINDEX(@separator, @givenString, [end] + 1)         FROM           data         WHERE [end] > 0   )   SELECT     ROW_NUMBER() OVER (       ORDER BY OrdinalPosition     ) OrdinalPosition,     RTRIM(LTRIM(Value)) + (       CASE         WHEN @returnSeparator = 1           THEN             @separator         ELSE           ''       END     ) Value   FROM     (       SELECT         ROW_NUMBER() OVER (         ORDER BY [start]         ) OrdinalPosition,         SUBSTRING(@givenString, [start], COALESCE(NULLIF([end], 0), LEN(@givenString) + 1) - [start]) Value         FROM           data     ) r   WHERE RTRIM(Value) <> ''     AND Value IS NOT NULL   )