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?

Answer 1

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