For an instructor lead, in-depth look at learning SQL click below.
The STRING_SPLIT function in SQL Server is a table-valued function that splits a string into rows of substrings, based on a specified separator. Introduced in SQL Server 2016 (13.x), it has proven to be a handy tool when it comes to dealing with string manipulation tasks.
Syntax
The basic syntax of STRING_SPLIT function in SQL is as follows:
|
1 2 3 |
STRING_SPLIT ( string , separator ) |
How does STRING_SPLIT work?
STRING_SPLIT function takes two parameters:
- string: This is the original string which is needed to be split.
- separator: This is the delimiter based on which the string will be split.
The function will return a single-column table, with a row for each substring.
Example
Consider the following example where we have a single comma-separated string:
|
1 2 3 |
DECLARE @tags NVARCHAR(400) = 'SQL,Server,STRING_SPLIT,Function' |
If we wish to split this string based on the comma as a delimiter, we could use the following SQL:
|
1 2 3 4 |
SELECT value FROM STRING_SPLIT(@tags, ',') |
As a result, the SQL Server will return a table that looks something like this:
| value |
|---|
| SQL |
| Server |
| STRING_SPLIT |
| Function |
In conclusion, the STRING_SPLIT function in SQL Server is a simple yet powerful function that can assist you with string manipulation tasks involving split operations on strings.