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..
6,456 total views, 3 views today