This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

The new SQL String_Split function for comma-delimited strings
If you're using SQL Server with compatibility level 130 or greater you can now use the new String_Split function to divide a comma-delimited string into a table of values.

Posted by Andy Brown on 22 March 2021

In this blog

The basic syntax of the new String_Split function
String_Split returns a table
Checking your database's compatibility level

The new SQL String_Split function for comma-delimited strings

This is a subject which won't move many people, but means a lot to me! Here's a table:

A few of the 720,000+ skills assessment tests which have been started on our website.

Database geeks: please don't have a go at us for storing our data in denormalised form like this (I'm sure we had good reasons at the time).

So previously if I wanted to get at the table of questions or answers for a particular test, I'd have to use a table-defined function like this one. No longer!

The basic syntax of the new String_Split function

Users of SQL Server databases with compatibility level 130 or higher (see below for how to determine your compatibility level - in practice you'll need to be using SQL Server 2016 or later) can now use the String_Split function which takes two arguments:

Number	Argument
1	The string of text you want to split
2	The delimiter (often a comma, as for the example above)

Here's a really simple example:

This simple query splits the phrase Wise Owl Training into three words.

Here's another example using a comma as a delimiter:

-- split a shopping list by commas

SELECT

value AS Fruit

FROM

string_split(

'Apples,Bananas,,Cherries,,Pears',

','

)

This would return the empty items also, so it might be an idea to add a WHERE clause to strip these out:

There may be better ways to do this, but I just added WHERE LEN(LTRIM(RTRIM(value))) > 0 to my query to get rid of the blank rows.

String_Split returns a table

The String_Split function returns a table of data, which means that a query like this won't work:

The message here is really misleading: the function does exist, but can't be used as if it returns a single value (or scalar).

To prove this, if you use a subquery to count the number of rows returned from the call to this function for each row, it will work:

-- show the questions for each test

-- taken in 2021

SELECT

t.TestId,

(

SELECT COUNT(*)

FROM String_Split(t.QuestionString,',')

) AS NumberQuestions

FROM

tblTest as t

WHERE

YEAR(t.DateCreated) = 2021

This would give output like this:

Most of our skills assessment tests contain 20 questions.

to get a list of the questions for each test for our example you could use the CROSS APPLY join:

-- show the questions for each test

-- taken in 2021

SELECT

t.TestId,

value AS QuestionNumber

FROM

tblTest as t

CROSS APPLY String_Split(t.QuestionString,',')

where

YEAR(t.DateCreated) = 2021

This would put each question number for each test on a separate row:

There will now be a list of the 20 question numbers for each test number.

The String_Split function always returns a single column, which SQL always gives the name value.

Checking your database's compatibility level

To do this, right-click on your database's name in Management Studio:

Right-click on the name of a database and choose Properties, as shown.

You can then see (and change) your database's compatibility level on the Options tab:

Phew - this database is already on compatibility level 130 or higher.

Some other pages relevant to the above blog include:

This blog has 0 threads

Add a new post

BLOGS BY TOPIC

BLOGS BY AUTHOR

The new SQL String_Split function for comma-delimited strings

The basic syntax of the new String_Split function

String_Split returns a table

Checking your database's compatibility level

Head office

London

Manchester