Yesterday, I was writing some Transact SQL to dust off the cobwebs. I got confused when I was playing around with the STRING_SPLIT function, and kept getting the error:

Msg 195, Level 15, State 10, Line 2
'STRING_SPLIT' is not a recognized built-in function name.

I checked my database compatibility level, which must be 130 or higher for the function to be available. My database compatibility level was 160, so that was fine.

The issue? STRING_SPLIT is a table valued function. If your syntax uses it as a scalar function, you will still see a message that it doesn’t exist – but really the issue is that the function exists, but needs to be called with a different syntax.

Let’s look at some examples.

Check and set your database compatibility level

This query will return the compatibility level for the current database:

SELECT
    d.compatibility_level
FROM sys.databases AS d
WHERE d.name = DB_NAME();

If your database compatibility level is less than 130, the STRING_SPLIT() built in function won’t be available.

You can read documentation on the differences between compat levels, which is helpful. It’s usually a good idea to make any compatibility levels in development environments first and burn them in for a while before making a change in production.

To update database compatibility level, run a command like:

ALTER DATABASE
    database_name
SET COMPATIBILITY_LEVEL = 160;

How to NOT use STRING_SPLIT

Here’s what I tried to use that didn’t work:

SELECT TOP (100)
    p.Id,
    split_test = STRING_SPLIT(p.Tags, '<', 1)
FROM dbo.Posts AS p;

and

SELECT
    split_test = STRING_SPLIT('a,b,c', ',', 1);

Both of these return the error:

Msg 195, Level 15, State 10, Line 1
'STRING_SPLIT' is not a recognized built-in function name.

What both of these queries have in common is that I’m trying to use the STRING_SPLIT function in the SELECT clause, as if it’s a scalar function. However, it’s a table valued function, so we need to do this another way.

Examples of successfully using STRING_SPLIT

You can use STRING_SPLIT with values that are already in a table, or with a list of supplied values.

STRING_SPLIT on a column with APPLY

For my first query, I want to run the STRING_SPLIT function against the Tags column in the Posts table.

Since STRING_SPLIT is a table valued function, I can do that with CROSS APPLY or OUTER APPLY syntax, like this:

SELECT TOP (100)
    p.Id,
    split_test.value
FROM dbo.Posts AS p
CROSS APPLY STRING_SPLIT(p.Tags, '<') AS split_test;

Notice that I didn’t name the column value. As a table valued function, STRING_SPLIT has already pre-defined the names of the columns it returns. The ‘Value’ column returns substrings after splitting using the specified separator.

If you are running SQL Server 2022 or higher, Azure SQL Database, or Azure SQL Managed Instance, you can optionally set the enable_ordinal parameter to true to return the “ordinal” column, which indicates the order the substring had in the original string.

The syntax for that is:

SELECT TOP (100)
    p.Id,
    split_test.value,
    split_test.ordinal
FROM dbo.Posts AS p
CROSS APPLY STRING_SPLIT(p.Tags, '<', 1) AS split_test;

STRING_SPLIT on literal values

The following syntax will return a single column named value with three rows, one row containing each letter in the string:

SELECT
    split_test.value
FROM STRING_SPLIT('a,b,c', ',') AS split_test;

I hope these examples save someone a few minutes of confusion.