Click here to Skip to main content
Click here to Skip to main content

Tagged as

Yet another string splitter

, 2 Oct 2011 CPOL
Rate this:
Please Sign up or sign in to vote.
Splits string using TSQL, but a very simple technique using the XML datatype.

You can use a very simple technique to split strings using T-SQL. No more while loops.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION dbo.SplitValues
(	
	@str			varchar(300),
	@demiliter		char(1)
)
RETURNS @TblList	table 	
(	val		varchar(20) )
AS
BEGIN 
 
	declare @xml xml
	SELECT @xml = CONVERT(xml,'<root><s>' + 
           REPLACE(@str, @demiliter,'</s><s>') + 
           '</s></root>')
	insert into @TblList
	select T.c.value('.','varchar(20)')
	FROM @xml.nodes('/root/s') T(c)
	return
END
GO

Test the function like this:

select * from dbo.SplitValues('abc,def,ghi,klm', ',')

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

zvin

Canada Canada
No Biography provided

Comments and Discussions

 
-- There are no messages in this forum --
| Advertise | Privacy | Terms of Use | Mobile
Web03 | 2.8.150414.1 | Last Updated 2 Oct 2011
Article Copyright 2011 by zvin
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid