Parse String SSIS

  • Hello, I want to parse a string in SSIS. My strings looks like this:

    CAT@@@DOG@@@yyyy

    MOUSE@@@bbb@@@aaa

    r@@@s@@@g

    They are separated by @@@ and I need code that is able to pull the 1st or 2nd or 3rd characters.

    Thanks.

  • try looking at the regular expressions. Here is just one article on this: http://msdn.microsoft.com/en-us/magazine/cc163473.aspx

  • Here is a splitter TVF that returns each data value as a row in a table. It relies on some SQL XML magic.

    /*

    USAGE:

    SELECT [Value] FROM [dbo].[split_delimited_string]

    ('1||2||3||4||5||6||7||8||9||10||11||12||13||14||15||16||17||18||19||20','||')

    WHERE Value IN (1,2,3,4,5,6,7,8,9,20)

    SELECT [Value] FROM [dbo].[split_delimited_string]

    ('1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20',';')

    SELECT [Value] FROM [dbo].[split_delimited_string]

    ('1[][]2[][]3[][]4[][]5[][]6[][]7[][]8[][]9[][]10[][]11[][]12[][]13[][]14[][]15[][]16[][]17[][]18[][]19[][]20','[][]')

    */

    CREATE FUNCTION [dbo].[split_delimited_string]

    (

    @STR NVARCHAR(MAX),

    @sep NVARCHAR(MAX)

    )

    RETURNS @value TABLE (Value NVARCHAR(MAX))

    AS

    BEGIN

    DECLARE @xml XML

    SELECT @xml = CONVERT(XML,'<r>' + REPLACE(@str,@sep,'</r><r>') + '</r>')

    INSERT INTO @value(Value)

    SELECT t.value('.','NVARCHAR(MAX)')

    FROM @xml.nodes('/r') AS x(t)

    RETURN;

    END

  • Recommend using Script Component in the Data flow. The split function is normally faster than anything in T-SQL and less complicated than most other options.

  • Or maybe let SSIS do all the work by defining @@@ as a column delimiter.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • Phil Parkin (3/24/2014)


    Or maybe let SSIS do all the work by defining @@@ as a column delimiter.

    +1 for simplicity 😀

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Phil Parkin (3/24/2014)


    Or maybe let SSIS do all the work by defining @@@ as a column delimiter.

    touché

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply