CODEWITHSUNDEEP
sql-servert-sqlsplit
Chinmoy Bhowmik
Chinmoy Bhowmik

Reputation: 89

Alternative of string_split Function on lower compatibility level

I can not use string_split functions as for compatibility level problem. I know how to change the compatibility level. But for rapid development over the database, there exist some risks if any old features goes invalid.

Now, is there any alternatives of string_split function in compatibility level 110?

or what will be the function if I want to define it?

Upvotes: 5

Views: 22486

Answers (2)

critical_error
critical_error

Reputation: 6726

Here is how I have approached this in pre-string_split days, by converting the list to an XML string and then using SQL Server's XML support.

DECLARE @list varchar(255) = 'value1,value2,value3,value4,value5';

SELECT
    x.f.value( '.', 'varchar(50)' ) AS [value]
FROM ( 
    SELECT CAST ( '<v><i>' + REPLACE ( @list, ',', '</i><i>' ) + '</i></v>' AS xml ) AS x 
) AS d
CROSS APPLY x.nodes( '//v/i' ) x( f );

RETURNS

+--------+
| value  |
+--------+
| value1 |
| value2 |
| value3 |
| value4 |
| value5 |
+--------+

You could convert this into an inline table-valued-function:

CREATE OR ALTER FUNCTION dbo.my_string_split (  
    @list varchar(1000), @delim varchar(1) = ','
)
RETURNS TABLE 
AS
RETURN (
    SELECT
        x.f.value( '.', 'varchar(50)' ) AS [value]
    FROM ( 
        SELECT CAST ( '<v><i>' + REPLACE ( @list, @delim, '</i><i>' ) + '</i></v>' AS xml ) AS x 
    ) AS d
    CROSS APPLY x.nodes( '//v/i' ) x( f )
)
GO

To call it:

SELECT * FROM dbo.my_string_split( 'value1,value2,value3,value4,value5', ',' );

Upvotes: 9

Charlieface
Charlieface

Reputation: 72480

Since master usually has the latest compatibility level, you can always execute STRING_SPLIT there.

So we can create inline TVF functions to execute it, then mark it as a system function so you can access it anywhere.

USE master;
GO

CREATE FUNCTION STRING_SPLITV
(@string varchar(max), @separator char(1))
RETURNS TABLE AS RETURN

SELECT value FROM STRING_SPLIT(@string, @separator);
GO

CREATE FUNCTION STRING_SPLITN
(@string nvarchar(max), @separator nchar(1))
RETURNS TABLE AS RETURN

SELECT value FROM STRING_SPLIT(@string, @separator);
GO

EXEC sp_ms_marksystemobject 'STRING_SPLITV';
EXEC sp_ms_marksystemobject 'STRING_SPLITN';

You can now use these functions in every database

USE YourDB;
GO

SELECT * FROM STRING_SPLITV('a,b,c', ',');

Upvotes: 8

Related Questions