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