Msg 195: STRING_SPLIT is not a recognized built-in function name

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 compatibility_level 
FROM sys.databases 
WHERE 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,
    STRING_SPLIT(p.Tags, '<', 1) as split_test
FROM dbo.Posts as p;

and

SELECT 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.

Brief aside on this query

This example query uses the StackOverflow2010 dataset. The tag column in post uses a more complex delimiter than a single character, but the STRING_SPLIT function only allows a single character to be specified. This presents some extra data cleansing issues if you want to use STRING_SPLIT for this purpose with that data.

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 *
FROM STRING_SPLIT('a,b,c', ',');

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