Read our blogs, tips and tutorials
Try our exercises or test your skills
Watch our tutorial videos or shorts
Take a self-paced course
Read our recent newsletters
License our courseware
Book expert consultancy
Buy our publications
Get help in using our site
549 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owl trainers only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
Search our website
We also send out useful tips in a monthly email newsletter ...
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. |
In this blog
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!
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.
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.
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:
Kingsmoor House
Railway Street
GLOSSOP
SK13 2AA
Landmark Offices
99 Bishopsgate
LONDON
EC2M 3XD
Holiday Inn
25 Aytoun Street
MANCHESTER
M1 3AE
© Wise Owl Business Solutions Ltd 2024. All Rights Reserved.