42

In SQL Server 2016 I receive this error with STRING_SPLIT function

SELECT * FROM STRING_SPLIT('a,b,c',',')

Error:

Invalid object name 'STRING_SPLIT'.

gotqn
  • 37,902
  • 44
  • 152
  • 231
Victor Hugo Terceros
  • 2,750
  • 2
  • 17
  • 31

4 Answers4

72

Make sure that the database compatibility level is 130

you can use the following query to change it:

ALTER DATABASE [DatabaseName] SET COMPATIBILITY_LEVEL = 130

As mentioned in the comments, you can check the current compatibility level of a database using the following command:

SELECT compatibility_level FROM sys.databases WHERE name = 'Your-Database-Name';
Yahfoufi
  • 2,040
  • 1
  • 21
  • 38
  • 24
    "Simple, just change the gravitational constant of the universe" (permissions denied) – RJB Aug 08 '18 at 22:58
  • what if my sql server justo allow me 100,90 & 110 whitchshould i use? – E.Rawrdríguez.Ophanim Aug 30 '18 at 23:58
  • 3
    @E.Rawrdríguez.Ophanim then you cannot use `STRING_SPLIT` function. You can build your own function. check this link https://stackoverflow.com/questions/10914576/t-sql-split-string – Yahfoufi Sep 03 '18 at 11:09
  • 2
    Check your database compatibility by `SELECT compatibility_level FROM sys.databases WHERE name = 'Your-Database-Name';` – Muhammad Musavi May 21 '19 at 07:36
  • My mistake was to add single quote around my database name, e.g. `['DatabaseName']`, and you'll get error message "User does not have permission to alter database". Remember **DO NOT** add the single quote. Correct way: `[DatabaseName]` and you will be able to set the compatibility level – Coder Dev Sep 24 '21 at 05:05
9

I was using Split_string, which felt grammatically correct in English, but my old eyes didn't see that it should be STRING_SPLIT ... so, if you're a bit mentally challenged like me, then check you've typed the command the right way! :)

Muhammad Musavi
  • 2,186
  • 2
  • 19
  • 33
Toad
  • 328
  • 2
  • 11
4

If you can't change the COMPATIBILITY_LEVEL of the database you are working in, you can try looking at other databases on the same server to find one with a higher COMPATIBILITY_LEVEL. I found that the "master" database on my target server was at COMPATIBILITY_LEVEL = 140, so I connected to that database, and then executed my query, which actually ran against (fully qualified) tables in other databases on the server whose COMPATIBILITY_LEVEL was less than 130. It worked! Just query the various databases on your server with

SELECT compatibility_level  FROM sys.databases WHERE name = '<database_name>';

to find one that is >= 130.

tvarney
  • 41
  • 1
1

You need to change the Compatibility Level of your Database. to do so try below Query and Change you compatibility level to 150.

ALTER DATABASE [Database_Name] SET COMPATIBILITY_LEVEL = 150

Divy Mistry
  • 11
  • 1