I found a pretty cool little function as I was perusing the SQL Server functions documentation the other day. (What, doesn’t everyone do that?) I just read the name of this function and thought, well, no need to create that function with a loop any longer! Finally, SPLIT has come to SQL Server!

What is STRING_SPLIT?

Most programming languages have a SPLIT function, where you specify the character you want it to split on, and it returns an array. Here’s an example from the C# documentation.

string phrase = "The quick brown fox jumps over the lazy dog.";
string[] words = phrase.Split(' ');

foreach (var word in words)
{
System.Console.WriteLine($"<{word}>");
}

STRING_SPLIT is the SQL version of that, but instead of an array, it returns a table. After all, tables are the ultimate arrays!

How Does it Work?

STRING_SPLIT is a table-valued function that splits a character string into pieces. You just tell it what character to split the characters on, and split does the rest, returning a table.

You are probably familiar with CSV files. CSV stands for “Comma-Separated Values.” A string in a CSV file might look like this:

"Randy,Sims,BI Developer,Florida"

Let’s say we want to read this into a VARCHAR, but we want to pivot it. I don’t want a row of these values, I want a column of these values. Essentially, I want it to act like a field in a table. Enter: STRING_SPLIT.

DECLARE @csv VARCHAR(50) = 'Randy,Sims,BI Developer,Florida';

SELECT @csv csv

SELECT VALUE FROM STRING_SPLIT(@csv,',');

Results:

csv
--------------------------------------------------
Randy,Sims,BI Developer,Florida

(1 row affected)

VALUE
--------------------------------------------------
Randy
Sims
BI Developer
Florida

(4 rows affected)

Table-Valued means you can use it in a WHERE clause.

SELECT VALUE FROM STRING_SPLIT(@csv,',') WHERE value = 'Randy';

VALUE
--------------------------------------------------
Randy

(1 row affected)

 

What’s it Going to Cost Me?

This is a Compatability-Level 130 or Higher club only, I’m afraid. You have to have at least SQL Server 2016 and it has to be running in Compatability Level 130.

In Summary…

You can still impress your colleagues with fancy functions to return a table of CSV values, but you really don’t have to anymore if you have SQL Server 2016. Dazzle them with your knowledge of new functions instead! SPLIT_STRING brings the SPLIT to SQL Server.

References

Read some more about STRING_SPLIT in the Microsoft Documentation:

 

Tagged: , ,