SQL STRING_SPLIT function

SQL Server STRING_SPLIT function is used to split a string into a table that consists of rows of substrings based on a specified separator. This function was introduced in SQL Server 2016.

STRING_SPLIT requires the compatibility level to be at least 130. When the level is less than 130, SQL Server is unable to find the STRING_SPLIT function.

SYNTAX

STRING_SPLIT (inputstring, seprator)

inputstring

Is a string of any character type – nvarchar, varchar, nchar, or char

seprator
Is a single character of any character type nvarchar, varchar, nchar, or char   that is used as separator for splitting.

Lets look at an example of STTRING_SPLIT in SQL Server.

Following statement uses SPLIT_STRING function to parse a comma-separated list of values.

DECLARE @inputstring AS VARCHAR(25) = 'Microsoft,sql,server,2016'; 
SELECT Value FROM STRING_SPLIT(@inputstring, ',')

As you can see, STRING_SPLIT function splits a comma separated string into a single column table.

 

Using WHERE CLAUSE With SPLIT_STRING function

You can also use WHERE condition with SPLIT_Function to filter the result.

DECLARE @inputstring AS VARCHAR(30) = 'Microsoft,sql,server,,2016';
SELECT Value FROM STRING_SPLIT(@inputstring, ',')

As you can see, a blank value in result. To remove this blank value you can use where condition with STRING_SPLIT Function to restrict blank values.

DECLARE @inputstring AS VARCHAR(30) = 'Microsoft,sql,server,,2016';
SELECT Value FROM STRING_SPLIT(@inputstring, ',')
WHERE Value <> ''

You can see, now blank blank value is not coming in result.

Using SPLIT_STRIN Function With Cross Apply operator

Using SPLIT_STRING function on Tag column you can convert comma serrated string into single column table but also can add associated Items along with Tag in the result set using cross apply operator.

To demonstrate this, Lets create a sample table named Item_tagList  as shown below.

CREATE TABLE dbo.Item_tagList 
(
ItemId INT IDENTITY(1,1),
Item VARCHAR(30),
Tag VARCHAR(100) 
)

INSERT INTO Item_TagList(Item, Tag)
VALUES 
('Software', 'Windows,MsOffice,Sqlserver2016-setup,Notepad++'),
('Hardware', 'Mouse,HardDisk,PenDrive')

Now we have a table named Item_TagList  and It has a column Tag which contains comma-separate list of tags .

SELECT * FROM dbo.Item_tagList

Now, we want to split the Tag column values into single column values, which are associated with their Item Name.

SELECT ITL.Item, Value AS ItemTag 
FROM Item_tagList ITL CROSS APPLY STRING_SPLIT(Tag,',')

As you can see, now comma separated Tag string is converted into single columns along with associated Item.

Alternate way of splitting string

CREATE FUNCTION [dbo].[fn_split_string] (
@string NVARCHAR(MAX),
@delimiter CHAR(1)
)
RETURNS @output TABLE (
[id] INT IDENTITY(1, 1) NOT NULL,
[value] NVARCHAR(MAX)
)
AS
BEGIN
DECLARE @value NVARCHAR(MAX),
@pos INT = 0,
@len INT = 0

SET @string = CASE 
WHEN RIGHT(@string, 1) != @delimiter
THEN @string + @delimiter
ELSE @string
END

WHILE CHARINDEX(@delimiter, @string, @pos + 1) > 0
BEGIN
SET @len = CHARINDEX(@delimiter, @string, @pos + 1) - @pos
SET @value = SUBSTRING(@string, @pos, @len)

INSERT INTO @output ([value])
SELECT LTRIM(RTRIM(@value)) AS [column]

SET @pos = CHARINDEX(@delimiter, @string, @pos + @len) + 1
END

RETURN
END

Lets, execute the function fn_split_string.

DECLARE @inputstring AS VARCHAR(20) = 'Microsoft,sql,server,2016' 
SELECT * FROM [dbo].[fn_split_string](@inputstring,',') 

You can see, it splits the string into single column table.

Also Read..

CONCAT_WS()

REVERSE()

FORMAT()

STRING_AGG()

PATINDEX()

QUOTENAME()