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('' + REPLACE(@string, @seperator, '') + '' 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;