Alternative of string_split Function on lower compatibility level-CodePudding

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?

CodePudding user response：

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

CodePudding user response：

If you have any other database in compat 130 or better, you can create a function there:

USE OtherDB;
GO

CREATE FUNCTION dbo.SplitStrings(@s nvarchar(max), @d nchar(1))
RETURNS table WITH SCHEMABINDING
AS
  RETURN (SELECT value FROM STRING_SPLIT(@s, @d));
GO

GRANT SELECT ON dbo.SplitStrings TO [public];

Now, in your database, you can:

Call the function directly

 SELECT value FROM OtherDB.dbo.SplitStrings(@list, N',');

Create your own wrapper

 CREATE FUNCTION dbo.SplitStrings(@s nvarchar(max), @d nchar(1))
 RETURNS table
 AS
   RETURN (SELECT value FROM OtherDB.dbo.SplitStrings(@s, @d));
 GO

 SELECT value FROM dbo.SplitStrings(@list, N',');

Create a synonym

 CREATE SYNONYM dbo.SplitStrings FOR OtherDB.dbo.SplitStrings;
 GO

 SELECT value FROM dbo.SplitStrings(@list, N',');

Lots more on splitting strings here:

Splitting strings

CodePudding user response：

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