SQL Server 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()

SQL Basics Tutorial	SQL Advance Tutorial	SSRS	Interview Q & A
SQL Create table	SQL Server Stored Procedure	Create a New SSRS Project	List Of SQL Server basics to Advance Level Interview Q & A
SQL ALTER TABLE	SQL Server Merge	Create a Shared Data Source in SSRS	SQL Server Question & Answer Quiz
SQL Drop	SQL Server Pivot	Create a SSRS Tabular Report / Detail Report
..... More	.... More	....More
Power BI Tutorial	Azure Tutorial	Python Tutorial	SQL Server Tips & Tricks
Download and Install Power BI Desktop	Create an Azure storage account	Learn Python & ML Step by step	Enable Dark theme in SQL Server Management studio
Connect Power BI to SQL Server	Upload files to Azure storage container		SQL Server Template Explorer
Create Report ToolTip Pages in Power BI	Create Azure SQL Database Server		Displaying line numbers in Query Editor Window
....More	....More		....More

 6,456 total views,  3 views today