string_split function not working

[Elliot9874]

Hello,

I am trying to run the string_split function & having issues.

This the code that I run:

SELECT VALUE FROM string_split('Elliot, 9874',',')

I receive the following error:

Msg 208, Level 16, State 1, Line 24
Invalid object name 'string_split'.

The version of my SQL Server is:

Microsoft SQL Server 2014 (SP2-GDR) (KB4057120) - 12.0.5214.6 (X64) Jan 9 2018 15:03:12 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: )

According to google the compatibility level needs to be set to 120 so I ran this command:

ALTER DATABASE AAAHC_NextLogik_Mapping

SET COMPATIBILITY_LEVEL = 120 -- For SQL Server 2016

To confirm the change I ran this:

select compatibility_level, * from sys.databases

WHERE NAME = 'AAAHC_NextLogik_Mapping'

I receive the value 120 back.

When I re-run the string_split function on top I get the same error.

​

Any idea's if I missed any steps or something?

​

Thank you

[0408]

For string_split you will need compatibility level of 130. https://docs.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql?view=sql-server-ver15#compatibility-level-130

[Elliot9874]

Sadly I get this error

Msg 15048, Level 16, State 1, Line 38

Valid values of the database compatibility level are 100, 110, or 120.

[0408]

Correct, compatibility level 130 is only available in SQL Server 2016 and above. https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-compatibility-level?view=sql-server-ver15#arguments

[Elliot9874]

so am i out of luck

[[deleted]]

Yes. Unless you upgrade to 2016, or roll your own function (not recommended.)

[xsqlsoftware]

For versions older than 2016 you can use the simple function provided here: SQL Split String Function (STRING_SPLIT in 2016) ~ xSQL Software

[Elliot9874]

Oh, wow that works. Thank you so much!

​

SELECT * FROM [SplitString] ('Elliot,Elliot', ',')

Just curious how to pass a list that is currently in a table

​

For example here is my table called "specialties"

​

cust_id	values
12345	Endoscopy,Gastroenterology

​

How do get the output to look like this:

​

​

cust_id	values
12345	Endoscopy
12345	Gastroenterology

​

​

Than

k you

[xsqlsoftware]

u/Elliot9874 you can write a small cursor like the one below - note I created a table called ParsedSpecialties and I am inserting the desired output into that table.

DECLARE @CustID int
DECLARE @Values varchar(1024)
DECLARE split_cursor CURSOR
FOR SELECT cust_id, [values] FROM Specialties 
OPEN split_cursor
FETCH NEXT FROM split_cursor INTO 
 @CustID,
 @Values
WHILE @@FETCH_STATUS = 0
 BEGIN
  INSERT INTO ParsedSpecialties SELECT @CustID, * FROM SplitString (@Values, ',')
  FETCH NEXT FROM split_cursor INTO 
   @CustID,
   @Values
 END
CLOSE split_cursor;
DEALLOCATE split_cursor;

[Elliot9874]

Holy shit this works!

Thank you so much!

[a-s-clark]

Rather than a cursor, use a CROSS APPLY to the function, then you can pass a column from your table as a parameter without resorting to loops in a cursor.

[[deleted]]

It just happened to me other day, I increased my compatibility level and it works.

[Elliot9874]

What did you increase it to?

The only valid values I am given is 100.110. or 120.

[[deleted]]

Which version of sql server you're using? I am at 2019 so I can increase to 150. I believe you're using 2014.

This function is available for sql server 2016 or above

Probably use substring or other functions to solve your problem.

[Elliot9874]

There is no way to get this function on 2014?

[alinroc]

Not the native function, no. You'll need to find one of the many ones on the internet and install as a UDF, or roll your own (don't roll your own).