Alternative of string_split Function on lower compatibility level

Question

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?

I guess you can take a look on this topic https://stackoverflow.com/questions/10914576/t-sql-split-string — Sergey, Nov 11 '21 at 08:21
There are all kinds of string splitting UDFs for versions prior to 2016 - https://sqlperformance.com/2021/09/t-sql-queries/split-strings — Zohar Peled, Nov 11 '21 at 08:37
You can execute your query in `master`, that will always work on 2016 — Charlieface, Nov 11 '21 at 11:54

score 5 · Answer 1 · answered Nov 11 '21 at 12:05

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

Nice trick! I didn't know about `sp_ms_marksystemobject`, thanks, I've learned something new today. — Zohar Peled, Nov 14 '21 at 09:35

score 5 · Accepted Answer · answered Nov 11 '21 at 14:17

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

Alternative of string_split Function on lower compatibility level

2 Answers2

Linked

Related