I want some Moore

Blog about stuff and things and stuff. Mostly about SQL server and .Net
posts - 219, comments - 2287, trackbacks - 33

My Links

Advertisement

News

Hi! My name is 
Mladen Prajdić  I'm from Slovenia and I'm currently working as a .Net (C#) and SQL Server developer.

I also speak at local user group meetings and conferences like SQLBits and NT Conference
Welcome to my blog.
SQL Server MVP

My Books

SQL Server MVP Deep Dives 2
The Red Gate Guide to SQL Server Team based Development Free e-book

My Blog Feed via Email
Follow MladenPrajdic on Twitter


Users Online: who's online

Article Categories

Archives

Post Categories

Cool software

Other Blogs

Other stuff

SQL stuff

SQL Server 2005: Split string XML Style

Here's a Split function using XML datatype.

It's preety neat and simple compared to all others that i've seen.

Forget While Loops and recursive CTE's.

Enter XML: 

 

IF OBJECT_ID('dbo.Split') IS NOT NULL
    DROP FUNCTION dbo.Split

GO
CREATE FUNCTION dbo.Split(@data NVARCHAR(MAX), @delimiter NVARCHAR(5))
RETURNS @t TABLE (data NVARCHAR(max))
AS
BEGIN
    
    DECLARE @textXML XML;
    SELECT    @textXML = CAST('<d>' + REPLACE(@data, @delimiter, '</d><d>') + '</d>' AS XML);

    INSERT INTO @t(data)
    SELECT  T.split.value('.', 'nvarchar(max)') AS data
    FROM    @textXML.nodes('/d') T(split)
    
    RETURN
END
GO

DECLARE @text NVARCHAR(max)
SELECT @text = REPLICATE('ab,', 300) + 'ab'

SELECT * FROM dbo.Split(@text, ',')

 

Enjoy it. :) kick it on DotNetKicks.com

Print | posted on Tuesday, May 22, 2007 7:23 PM | Filed Under [ SQL Server ]

Feedback

Gravatar

# re: SQL Server 2005: Split string XML Style

cool!
5/23/2007 9:18 AM | Mladen
Gravatar

# re: SQL Server 2005: Split string XML Style

thanks
6/15/2009 4:42 PM | Halil Güler
Gravatar

# re: SQL Server 2005: Split string XML Style

I have a ntext field that I want to use this function on but it's not vorking on ntext data type.
Any solutions on that.

Thank You,
N.T.
10/2/2009 11:04 PM | sqlgirl
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET