BLOGS BY TOPIC▼
BLOGS BY AUTHOR▼
BLOGS BY YEAR▼
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:
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.