
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 there is 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 values 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.
Without using STRING_SPLIT Function, you have to write a long T-SQL Query to splitting the 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..
833 total views, 2 views today