Post reply

Syntax help - split string into separate components

  • February 27, 2011 at 9:09 pm

    #235235

    I have a column with a semi colon delimited list, which contains a 2 item comma separated list within.

    I would like to split the string into multiple columns, one for each semi-colon separated item, preferably with the column name being the first item in comma list and value being the second item in comma separated list.

    i.e.

    DateStartField,2011-01-01 10:00:00;DateEndField,2011-02-01 10:00:00;FieldOne,102.223;FieldTwo,233

    Would result in 4 columns DateStartField,DateEndField, FieldOne and FieldTwo with respective values.

  • February 28, 2011 at 7:53 am

    #1292324

    This takes a little bit of:

    1. Splitting the string apart into it's delimited parts, and

    2. Utilizing some dynamic sql.

    For the first part, we'll utilize the DelimitedSplit8K function. Since there are two embedded parts of the strings, we'll have to nest the calls. Here is the latest version of the Delimited Split Function - but stay tuned - a newer, faster version is in the works!

    And here is the resultant code:

    DECLARE @test VARCHAR(500);

    SET @test = 'DateStartField,2011-01-01 10:00:00;DateEndField,2011-02-01 10:00:00;FieldOne,102.223;FieldTwo,233';

    DECLARE @SqlCmd VARCHAR(MAX);

    SET @SqlCmd = 'DECLARE @test varchar(500);

    SET @test = ' + QUOTENAME(@test, CHAR(39)) + ';

    SELECT ' + STUFF((

    SELECT ',' + QUOTENAME(ds2.ItemValue) + ' = MAX(CASE WHEN ds1.ItemNumber = ' + CONVERT(VARCHAR(10), ds1.ItemNumber) + ' THEN ds2.ItemValue ELSE NULL END)'

    FROM dbo.DelimitedSplit8k(@test, ';') ds1

    CROSS APPLY dbo.DelimitedSplit8k(ds1.ItemValue, ',') ds2

    WHERE ds2.ItemNumber = 1 -- get just the column names

    FOR XML PATH(''),TYPE).value('.','varchar(max)'),1,1,'') + '

    FROM dbo.DelimitedSplit8K(@Test, '';'') ds1

    CROSS APPLY dbo.DelimitedSplit8K(ds1.ItemValue, '','') ds2

    WHERE ds2.ItemNumber > 1 -- get all data except for the column names

    GROUP BY ds2.ItemNumber';

    --PRINT @SqlCmd; -- debugging purposes

    EXECUTE (@SqlCmd);

    which returns this result set:

    DateStartField DateEndField FieldOne FieldTwo

    ------------------- ------------------- -------- --------

    2011-01-01 10:00:00 2011-02-01 10:00:00 102.223 233

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • February 28, 2011 at 2:55 pm

    #1292612

    Thanks!

Viewing 3 posts - 1 through 3 (of 3 total)

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