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'.
-
3Check the compatibility level of the database you are running it from. You may have it set lower.S3S– S3S2017-11-09 15:37:42 +00:00Commented Nov 9, 2017 at 15:37
-
3Make sure that the database compatibility level is 130.Lukasz Szozda– Lukasz Szozda2017-11-09 15:41:39 +00:00Commented Nov 9, 2017 at 15:41
-
11There's a big honking blue box in the documentation concerning this topic.Jeroen Mostert– Jeroen Mostert2017-11-09 15:41:47 +00:00Commented Nov 9, 2017 at 15:41
-
@JeroenMostert +1 for use of the word "honking" :)DavidG– DavidG2017-11-09 15:42:29 +00:00Commented Nov 9, 2017 at 15:42
-
2Life's too short to read documentation :)Wouter– Wouter2018-06-11 11:42:50 +00:00Commented Jun 11, 2018 at 11:42
|
Show 2 more comments
6 Answers
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';
6 Comments
RJB
"Simple, just change the gravitational constant of the universe" (permissions denied)
E.Rawrdríguez.Ophanim
what if my sql server justo allow me 100,90 & 110 whitchshould i use?
Yahfoufi
@E.Rawrdríguez.Ophanim then you cannot use
STRING_SPLIT function. You can build your own function. check this link stackoverflow.com/questions/10914576/t-sql-split-string
Muhammad Musavi
Check your database compatibility by
SELECT compatibility_level FROM sys.databases WHERE name = 'Your-Database-Name';
chesney C.
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 |
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! :)
Comments
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.
Comments
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
2 Comments
Alexander Volok
SQL Server 2016 does not have compatibility level 150. It is limited to 130 there and Yahfoufi answer is correct
tanuk
You should change the compatibility level value to 140 if you want to set your database compatibility to SQL Server 2017.
First of all, check version of your SQL server by:
SELECT @@version
If it's 2014 or older i.e. 12.x or older it won't support this STRING_SPLIT function.
If that's not the case, now it could be possible that your database has lower compatibility version (maybe you restored it from a lower version BAK or something else)
If using SSMS (SQL server management studio)
Right click 'YOUR_DATABASE' > Properties > Options > Compatibility Level
From the dropdown you should be able to select SQL Server 2016 (130) or higher
If not using SSMS use this Update script:
ALTER DATABASE [YOUR_DATABASE] SET COMPATIBILITY_LEVEL = 130
Comments
STRING_SPLIT is not a valid command for your SQL version and altering database might be very risky. Below can be used with both old and new version of SQL Servers and their databases. I know it is a big query but gets the job done without problem.
DECLARE @String varchar(1024)
DECLARE @Pos int
DECLARE @Keywords TABLE(string varchar(25))
DECLARE @Key varchar(25)
SET @String = 'a,b,c'
SET @Pos = 1
WHILE (@Pos > 0)
BEGIN
SET @Pos = PATINDEX('%,%', @String)
SET @Key = SUBSTRING(@String, 0, @Pos)
IF (@Pos = 0)
BEGIN
INSERT INTO @Keywords (string) VALUES(@String)
END
ELSE
BEGIN
INSERT INTO @Keywords (string) VALUES(@Key)
END
SET @String = SUBSTRING(@String, @Pos+1, (SELECT LEN(@String)))
END
SELECT * FROM @Keywords
2 Comments
T N
Another user suggested changing
@Pos to @Pos+1 in the second substring operation, and I agree. This would allow the delimiter to be other than a space - e.g., '%,%' instead of '% %'. The LTRIM() would then be unnecessary. Whether or not the split values should be trimmed and/or empty values discarded should really be separate from the split. See this fiddle.
Panagiotis Kanavos
This is the slowest possible split implementation and just adds noise instead of helping. There are a lot of answers that show various string splitting techniques already, but all in one way or another are derived from Aaron Bertrand's article on string splitting. The fastest way after a custom SQLCLR UDF is to use XML methods.
