Browse 554 attributed reviews, viewable separately for our classroom and online training
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

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

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:

Table of questions

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:

Simple split

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:

Removing blank items

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:

String_split not working

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:

Results of query

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:

Questions listed

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:

Showing database properties

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:

Database options

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

This blog has 0 threads Add post