Splitting Strings in T-SQL Using XML

Having this post:

• http://springboardpillow.blogspot.com/2009/04/splitting-strings-in-t-sql.html

And later finding this forum entry:

• http://www.devnewsgroups.net/group/microsoft.public.sqlserver.xml/topic64815.aspx

I have come up with my own SQL which does not require a function in order to take a series of pairs of integers and split them into a two-field result set:

Which when run on it's own, will output this:

The effect here is that a string passed in as a parameter can contain pairs (rows) of integers; each column in the rows being separated by ':' and each row being separated by ','.

This is aggregated into a single XML element, which can then be parsed into specific types in a result set (table) format.

Note: Sorry for the images, but blogger.com's online editor would not let me paste the XML source.

Splitting Strings in T-SQL

[EDIT] A very good collection of solutions:

• http://stackoverflow.com/questions/314824/t-sql-opposite-to-string-concatenation-how-to-split-string-into-multiple-recor

From an ex-colleague (with some alteration):

Create Procedure ReturnStringAsTable

(

@REQUEST VARCHAR(500),

@dividingChr VARCHAR(1) = ','

)

AS

BEGIN

DECLARE @CURRENT_TABLE VARCHAR(200);

DECLARE @INDEX_TO INT;

DECLARE @INDEX_FROM INT;

DEClARE @DATASETINDEX TABLE(ID INT IDENTITY(1,1),[NAME] VARCHAR(200))

SET @INDEX_FROM=0;

SET @INDEX_TO= CHARINDEX(@dividingChr,@REQUEST,@INDEX_FROM);

SET @INDEX_TO= CASE WHEN @INDEX_TO <=0 THEN LEN(@REQUEST)+1 ELSE @INDEX_TO end;

WHILE @INDEX_FROM <>

BEGIN

SET @CURRENT_TABLE=RTRIM(LTRIM(SUBSTRING(@REQUEST,@INDEX_FROM,@INDEX_TO-@INDEX_FROM)))

IF LEN(@CURRENT_TABLE)>2

BEGIN

INSERT INTO @DATASETINDEX([NAME]) VALUES(@CURRENT_TABLE);

EXEC ('SELECT * FROM ' + @CURRENT_TABLE)

END

SET @INDEX_FROM = @INDEX_TO+1

SET @INDEX_TO= CHARINDEX(',',@REQUEST,@INDEX_FROM);

SET @INDEX_TO = CASE WHEN @INDEX_TO > @INDEX_FROM THEN @INDEX_TO ELSE LEN(@REQUEST)+1 END;

END

INSERT INTO @DATASETINDEX([NAME]) VALUES('INDEX_TABLE');

SELECT * FROM @DATASETINDEX

END

Slightly cleaned up version of code found at: http://codebetter.com/blogs/raymond.lewallen/archive/2005/10/26/133774.aspx

CREATE FUNCTION Split(

@IDValues as varchar(500),

@Delimeter as varchar(1)

)

RETURNS @tValues TABLE(IDAux INT)

AS

BEGIN

DECLARE @I AS INT, @Aux AS INT

SET @I = 1

SET @Aux = 1

WHILE @I <= LEN(@IDValues)

BEGIN

IF SUBSTRING(@IDValues,@I,1)= @Delimeter

BEGIN

INSERT INTO @tValues (IDAux) VALUES (CAST(SUBSTRING(@IDValues,@Aux,(@I-@Aux))AS INT))

SET @Aux = @i + 1

END

SET @i = @i + 1

END

RETURN

END

GO

--examples with differents delimeters

select * from Split('1058,1059,1060,1062,',',')

select * from Split('1058|1059|1060|1062|','|')

select * from Split('1058;1059;1060;1062;',';')