'String_Split' Is Not a Recognized Built-in Function Name
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...
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?
3 Answers
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.', ' ');
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
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
Elena Rostova holds a Master's degree in Public Health Journalism. She covers groundbreaking medical research, holistic wellness trends, mental health awareness, and nutritional science.
