Post reply

Extracting data from string

  • December 1, 2008 at 2:28 pm

    #127299

    Hi,

    I have to write a procedure to extract data from string.

    String looks like this:

    '1,2,A,B,C~3,4,D,E,F~5,6,G,H,I'

    Records are separated by '~' and columns within record are separated by ','.

    I wrote a function that extracts string to table for any single character delimiter. I use it to separate out the records first.

    like select * from dbo.udf_ExtractString('1,2,A,B,C~3,4,D,E,F~5,6,G,H,I', '~')

    Output:

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

    1,2,A,B,C

    3,4,D,E,F

    5,6,G,H,I

    Now I can use the same function again with comma as delimiter to separate the columns from each record.

    like

    select * from dbo.udf_ExtractString('1,2,A,B,C', ',')

    Output

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

    1

    2

    A

    B

    C

    The problem is that the data is added as separate records. I need to convert them as column in a single row. Any suggestion on how to do it? I tried to use Pivot but not much luck.

    I guess I can use the same logic that I used to write the above function and store the values in variables and insert ina temp table. However, I would still like to know how to convert these data in one column to one row.

    I really appreciate your help.

    Thanks,

  • December 1, 2008 at 2:45 pm

    #905940

    I'm confused. Can you post an example of how you would like the data to look in its final stage?

  • December 1, 2008 at 2:52 pm

    #905948

    1,2,A,B,C are the column data in my table.

    This is the final result I am trying to get:

    Table: StrResults

    col1 col2 col3 col4 col5

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

    1 2 A B C

  • December 1, 2008 at 6:17 pm

    #905996

    Basically, you're trying to pass a "table" or "array" of data in that it has rows and columns. Please see the following article...

    [font="Arial Black"]

    Passing Parameters as (almost) 1, 2, and 3 Dimensional Arrays

    [/font][/url]

    --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 4 posts - 1 through 3 (of 3 total)

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