STRING_SPLIT Function In SQL Server

STRING_SPLIT function is a table-valued function used to convert a set of strings into row-level substrings. The function consists of a specified separator character to separate between the strings.

Please note that the STRINT_SPLIT function works on SQL Server 2016 and above.

If you experience "Invalid object name STRING_SPLIT", please click on the link here for the fix.

General Syntax:

STRING_SPLIT ( string , separator )

String – It accepts character types of nvarchar, varchar, nchar and char
Separator – It accepts character type of nchar(1) and char(1)

Simple Example:

select * from STRING_SPLIT ('hospital,office,home',',')

Example of STRING_SPLIT usage by passing multiple separator characters in the string field:

select * from STRING_SPLIT ('hospital,,,office,home',',')

Example of STRING_SPLIT usage by passing a different character other than a comma:

select * from STRING_SPLIT ('hospital$office$home','$')

Example of STRING_SPLIT usage by passing multiple special characters in separator field:

Here, we can notice that while applying multiple special characters in the separator field, the SQL errors out as the separator can accept a maximum of a single character.

 select * from STRING_SPLIT ('hospital,office,home',',,')

Example of STRING_SPLIT usage by passing strings to a parameter:

DECLARE @split NVARCHAR(400) = 'hospital,office,home'
SELECT value 
FROM STRING_SPLIT(@split, ',') 
WHERE RTRIM(value) <> '';

Example of STRING_SPLIT in WHERE clause:

select name from sys.databases
where name in
(select value from STRING_SPLIT ('msdb,tempdb,master,model',','))

Example of STRING_SPLIT by using CROSS APPLY

I have created a simple table as ‘Hospital’ and insert some sample records.

The data in the name column consists of a list of strings separated by a comma. This pattern of string can be converted into a list of columns using the CROSS APPLY operator imposed on STRING_SPLIT function.

select HospitalID, value
from hospital
cross apply STRING_SPLIT(name, ',')

Bottom Line:

The STRING_SPLIT function introduced on SQL Server 2016 is one of the coolest features and they can be used in many forms at multiple places to split strings. Additionally, they are known to increase the performance of the query if you notice them carefully by using the execution plan.

• Tweet
• Share
• Share
• Share
• Share