'STRING_SPLIT' is not a recognized built-in function name

Question

The new STRING_SPLIT method is not available in my Azure SQL database. I had already ran ALTER DATABASE [DatabaseName] SET COMPATIBILITY_LEVEL = 130 a couple days ago, and I have verified the compatibility level is indeed set to 130. SELECT database_id, name, compatibility_level FROM sys.databases

Has anyone else been able to use the new method, and if so, did you need to do anything else to get it working?

Answer 1

It was a syntax error. I was attempting to use the function as scalar and not as table valued.

Correct Syntax: SELECT Value FROM STRING_SPLIT('Lorem ipsum dolor sit amet.', ' ');

Answer 2

The STRING_SPLIT function is available at compatibility level 130 or higher. If your database compatibility level is lower than 130, SQL Server will not be able to find and execute STRING_SPLIT function. You can change a compatibility level of database using the following command:

ALTER DATABASE DatabaseName SET COMPATIBILITY_LEVEL = 130

EDIT: Note that compatibility level 150 might be default even in new Azure SQL Databases.

For reference:

Version - Highest Compatibility Level - Lowest Available Level

• SQL 2019 - 150 - 100
• SQL 2017 - 140 - 100
• SQL 2016 - 130 - 100
• SQL 2014 - 120 - 100
• SQL 2012 - 110 - 90
• SQL 2008 - 100 - 80
• SQL 2005 - 90 - 80
• SQL 2000 - 80 - 80

Answer 3

The string_split function must be placed after the FROM clause. Either as a table value (as in the OP's answer) OR use the following to split each string in a column by "," (credit comment from @padigan):

SELECT * 
FROM infoTable
CROSS APPLY STRING_SPLIT(infoTable.myStrings, ',') 

I am unsure how to name the resulting column and it takes the default name value