Click here to Skip to main content
12,631,606 members (33,072 online)
Click here to Skip to main content
Add your own
alternative version

Tagged as

Stats

4.1K views
Posted

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

You may also be interested in...

Comments and Discussions

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