A SQL Function : Split or Reinventing the wheel

It has been far too long since my last post.

Lately I have been working on some operational tasks that include a log of data gathering. I am using powershell and sql for this and thus the next few posts are likely to move in this direction.

One of the first few things I found was a complete lack of a Split function in SQL. I have instances where I receive a delimited string from an external process and need to handle it for use in SQL.

I found the following function using XML and am reposting it here for future reference.  Thanks to http://blog.codelab.co.nz/2009/07/29/split-function-for-t-sql-using-xml/ for the code.

You simply call the function with 2 strings, the seperator and the string you want to split, and out pops a one column table with your results in each row.

select * from split (‘~’,’Strange things are~a foot at the circle-K‘)

RESULTS

val
Strange things are
a foot at the circle-K

CREATE FUNCTION [dbo].[Split](@sep VARCHAR(32), @s VARCHAR(MAX))

RETURNS @t  TABLE

(val VARCHAR(MAX))

AS

BEGIN

DECLARE @xml XML

SET @XML =N'<root><r>’+REPLACE(@s, @sep,'</r><r>’)+'</r></root>’

INSERTINTO @t(val)

SELECT r.value(‘.’,’VARCHAR(MAX)’)as Item

FROM @xml.nodes(‘//root/r’)AS RECORDS(r)

RETURN

END

Leave a Reply

Your email address will not be published. Required fields are marked *