'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?

score 132 · Accepted Answer · answered Jun 03 '16 at 14:46

132

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.', ' ');

answered Jun 03 '16 at 14:46

Gonkers

1,971
2
10
9

5

How would I split a string in one of my columns though? That is where the use is rather than substring indexing it and manipulating the string. – ThinkCode Jun 06 '16 at 18:26
19

@ThinkCode you need to use 'APPLY' e.g. something a little like this maybe `SELECT t.SomeColumn, s.value FROM dbo.MyTable t CROSS APPLY STRING_SPLIT(t.SomeColumn, ',') s` – padigan Jan 29 '18 at 11:34

Nilay Vishwakarma · Answer 2 · 2019-12-24T15:37:06.227

33

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

edited Dec 24 '19 at 15:37

answered Jan 16 '17 at 05:23

Nilay Vishwakarma

3,105
1
27
48

I tried to execute above query, but getting this message: Msg 15048, Level 16, State 1, Line 3 Valid values of the database compatibility level are 100, 110, or 120. – Prateek Gupta May 16 '17 at 14:25
@Prateek-Systematix Are you using SQL Server 2014? See my edit – Nilay Vishwakarma May 16 '17 at 14:38

johnDanger · Answer 3 · 2022-02-09T17:04:05.673

18

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

edited Feb 09 '22 at 17:04

answered Jan 10 '20 at 19:16

johnDanger

1,990
16
22

1

For me simply using `[VALUE] AS [categoryCodes]` allows me to rename the resulting column to whatever I like and that is in-line with the code that you have, you would just need to write out all columns as opposed to using the asterisk. – Ryan Harris Nov 22 '20 at 06:25

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

3 Answers3

Linked