Friday 3 June 2016

TSQL Snippet: Split string in records

Case
I have a string in TSQL and I want to split it into separate values / records. How do I do that?

Solution
There are a lot of split examples available on the web, but I really like the XQuery solution for this. First you add a begin XML tag in front of your list and an closing XML tag at the end. Then you replace all separators by a closing and a begin tag. After that you have an XML string and you can use Xquery to split it. Below a little snippet as part of a stored procedure, but you could also create a function for it or just use the three lines in your own code:

-- Snippet
CREATE PROCEDURE [dbo].[SplitList] (
      @List VARCHAR(255)
    , @Separator VARCHAR(1)
)
as
BEGIN
    DECLARE @Split XML;
    SET @Split = CAST('<t>' + REPLACE(@List, @Separator, '</t><t>') + '</t>' as XML) 
    SELECT Col.value('.', 'VARCHAR(255)') as ListValue FROM @Split.nodes('t') as xmlData(Col)  order by 1
END


Note: your string / list can't contain forbidden XML characters like <, > and &. You could use additional REPLACE functions to prevent errors: REPLACE(@List,"<", "&lt;")
split snippet

No comments:

Post a Comment

Please use the SSIS MSDN forum for general SSIS questions that are not about this post. I'm a regular reader of that forum and will gladly answer those questions over there.

All comments are moderated manually to prevent spam.

Related Posts Plugin for WordPress, Blogger...