Post reply

csv string

  • October 10, 2010 at 7:33 am

    #142233

    I have 2 csv string a follows..

    String1= ‘a,b,c,d’

    String2=’apple,boy,college,dog’

    I have to insert the table as

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

    index | Description

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

    a |Apple |

    b |Boy |

    c |college |

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

    Any help to achieve this?.... Thanx in advance

  • October 10, 2010 at 9:04 am

    #1234696

    There are multiple "split string" tvf functions you can find at SSC. Just use its search feature.

    Just add an identity column to the result table and join on that.

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • October 10, 2010 at 1:50 pm

    #1234716

    Using the DelimitedSplit8K function, you would end up with:

    DECLARE @String1 varchar(8000),

    @String2 varchar(8000);

    SET @String1 = 'a,b,c,d';

    SET @String2 = 'apple,boy,college,dog';

    SELECT a.Item, b.Item

    FROM dbo.DelimitedSplit8K(@string1, ',') a

    JOIN dbo.DelimitedSplit8K(@string2, ',') b

    ON a.ItemNumber = b.ItemNumber;

    Here is the latest version of the Delimited Split Function

    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

  • October 10, 2010 at 5:32 pm

    #1234731

    WayneS (10/10/2010)

    Using the DelimitedSplit8K function, you would end up with:

    DECLARE @String1 varchar(8000),

    @String2 varchar(8000);

    SET @String1 = 'a,b,c,d';

    SET @String2 = 'apple,boy,college,dog';

    SELECT a.Item, b.Item

    FROM dbo.DelimitedSplit8K(@string1, ',') a

    JOIN dbo.DelimitedSplit8K(@string2, ',') b

    ON a.ItemNumber = b.ItemNumber;

    Here is the latest version of the Delimited Split Function

    I guess I should add the latest performance enhancements to that and put it in the "Script Locker", huh?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • October 10, 2010 at 5:52 pm

    #1234736

    Jeff Moden (10/10/2010)

    WayneS (10/10/2010)

    Using the DelimitedSplit8K function, you would end up with:

    DECLARE @String1 varchar(8000),

    @String2 varchar(8000);

    SET @String1 = 'a,b,c,d';

    SET @String2 = 'apple,boy,college,dog';

    SELECT a.Item, b.Item

    FROM dbo.DelimitedSplit8K(@string1, ',') a

    JOIN dbo.DelimitedSplit8K(@string2, ',') b

    ON a.ItemNumber = b.ItemNumber;

    Here is the latest version of the Delimited Split Function

    I guess I should add the latest performance enhancements to that and put it in the "Script Locker", huh?

    I thought this had the lastest...???

    Can the code in the "Script Locker" be updated as it gets enhanced? How do we get access to it? (The scripts section here at SSC can't be updated... or at least I haven't figured out how to update the ones that I've put in there!)

    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

  • October 10, 2010 at 6:55 pm

    #1234740

    Heh... there's another couple of enhancements to make that came up recently... some for performance (2 to be precise) and some for those bloody "space" delimiters. I'm just not done testing, yet. 😛

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • October 11, 2010 at 12:03 am

    #1234789

    great!!!!!!! thank u jeff, wayne and ALZDBA for ur help....

  • October 11, 2010 at 12:09 am

    #1234792

    Hi Jeff...

    Could please tell me why you use following snipit in your split function?... whts it purpose exactly?

    WITH

    E1(N) AS ( --=== Create Ten 1's

    SELECT 1 UNION ALL SELECT 1 UNION ALL

    SELECT 1 UNION ALL SELECT 1 UNION ALL

    SELECT 1 UNION ALL SELECT 1 UNION ALL

    SELECT 1 UNION ALL SELECT 1 UNION ALL

    SELECT 1 UNION ALL SELECT 1 --10

    ),

    E2(N) AS (SELECT 1 FROM E1 a, E1 b), --100

    E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10,000

    cteTally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT N)) FROM E4)

  • October 11, 2010 at 12:32 am

    #1234794

    http://www.sqlservercentral.com/articles/T-SQL/62867/... i got ur article jeff 🙂

  • October 11, 2010 at 4:30 am

    #1234873

    mail4sha (10/11/2010)

    http://www.sqlservercentral.com/articles/T-SQL/62867/... i got ur article jeff 🙂

    So you're all set then?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • October 11, 2010 at 7:36 am

    #1234982

    yes ofcourse..... Perfect guidance... thanks jeff

  • October 12, 2010 at 7:14 am

    #1235494

    mail4sha (10/11/2010)

    yes ofcourse..... Perfect guidance... thanks jeff

    You bet. Thanks for the feedback.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 12 posts - 1 through 11 (of 11 total)

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