A Clone of the STRING_SPLIT MSSQL 2016 Function


Warning: count(): Parameter must be an array or an object that implements Countable in /home/fbsqlcom/public_html/youdidwhatwithtsql.com/wp-content/plugins/wp-codebox/main.php on line 31

I have recently been developing some stuff using MSSQL 2016 and used the STRING_SPLIT function. This doesn’t exist in earlier versions and I discovered I would be required to deploy to 2008 or 2012. So here’s a my own version of the STRING_SPLIT function I have developed and tested on MSSQL 2008 (may also work on 2005).

CREATE FUNCTION [dbo].[STRING_SPLIT_2008]
(
	@string VARCHAR(1024),
	@seperator CHAR(1)
)
	RETURNS @table TABLE (
		[Value] VARCHAR(1024)
	)
AS
BEGIN
 
	DECLARE @x XML;
	SELECT @x = CAST('<a>' + REPLACE(@string, @seperator, '</a><a>') + '</a>' AS XML);
 
	INSERT INTO @table
	SELECT t.value('.', 'varchar(1024)') as inVal
	FROM @X.nodes('/A') AS x(t)
 
	RETURN
END

Usage is as follows;

SELECT *
FROM dbo.STRING_SPLIT_2008('mail1.com;mail2.com;mail3.com;mail4.com;mail5.com;mail6.com;mail7.com;mail8.com;mail9.com', ';')

This would return the following resultset;

string_split_2008 resultset

Leave a Reply