Post reply

How to avoid loop using delimitedsplit8k

  • January 30, 2020 at 3:03 pm

    Go to Answer

    #3721631

    Hi,

    Below is the sample data

    Declare @text = '1,1000,FirstName1,LastName1|2,1001,FirstName2,LastName2|3,1002,FirstName3,LastName3|4,1003,'',LastName4
    |5,1004,FirstName5,'''

    Destination table structure :

    Create table Emp_Temp(Id int, IdProduct int,FirstName varchar(200), lastname varchar(200));

    Expected table values after parsing the delimiters.

    insert into Emp_Temp(id,IdProduct,FirstName,lastname)

    select 1, 1000,'FirstName1','LastName1' union all
    select 2, 1001,'FirstName2','LastName2' union all
    select 3, 1002,'FirstName3','LastName3' union all
    select 4, 1003,'','LastName4' union all
    select 5, 1004,'FirstName5',''

    I am using delimitedsplit8k function created by jeff. i can split the '|'. after that i have to iterate the splitted values and split ','. would it be possible to do this requirement without the loop? sometimes, we get firstname or lastname as empty

    Any sample query to achieve this would be highly helpful

  • January 30, 2020 at 3:35 pm

    #3721657

    do y0u have to use a delimited string?

    if it's a proc that's doing this then can you pass a table value parameter in rather than a delimited string?

    MVDBA

  • January 30, 2020 at 3:36 pm

    #3721658

    Something like this?!  It's only a start, obviously.

    SELECT DS2.*
    FROM dbo.DelimitedSplit8K(@text, '|') DS1
    CROSS APPLY dbo.DelimitedSplit8K(DS1.Item, ',') DS2

    SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

  • January 30, 2020 at 3:43 pm

    Answer

    #3721662

    Ignore the previous entry, this is much closer:

    SELECT 
        MAX(CASE WHEN DS2.ItemNumber = 1 THEN DS2.Item ELSE '' END) AS Id,
        MAX(CASE WHEN DS2.ItemNumber = 2 THEN DS2.Item ELSE '' END) AS IdProduct,
        MAX(CASE WHEN DS2.ItemNumber = 3 THEN DS2.Item ELSE '' END) AS FirstName,
        MAX(CASE WHEN DS2.ItemNumber = 4 THEN DS2.Item ELSE '' END) AS LastName
    FROM dbo.DelimitedSplit8K(@text, '|') DS1
    CROSS APPLY dbo.DelimitedSplit8K(DS1.Item, ',') DS2
    GROUP BY DS1.ItemNumber

    SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

  • January 30, 2020 at 3:58 pm

    #3721668

    too late!

  • January 30, 2020 at 4:30 pm

    #3721679

    thanks a lot scott. it helped me

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

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