Parse data colunm into 3 columns

  • I have data stored in a single varchar(500) column. It contains data that is pipe delimited, 3 pieces of information that I want to put in 3 seperate columns: Size, Placement, and URL. The data will be between less than and greater than signs with the pipe in between the 3 data fields.

    size|placement<04>|url<http://qa-media.custom.com/ugimages/076/401/500/tshirt.png>

    I am not sure how to approach this. Any suggestions?

    thanks,

    vmon

  • What have you tried so far?

    If have nothing to start with I'd like to recommend to search for "split string function" on this site. There are many solutions available.

    If you have problems in understanding of how those work or how to change it to meet your requirement let us know together with what you've tried so far.



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • vmon (9/17/2009)


    I have data stored in a single varchar(500) column. It contains data that is pipe delimited, 3 pieces of information that I want to put in 3 seperate columns: Size, Placement, and URL. The data will be between less than and greater than signs with the pipe in between the 3 data fields.

    size|placement|url

    I am not sure how to approach this. Any suggestions?

    thanks,

    vmon

    The forum "ate" some of the data vmon had tried to display... here's what the data actually looks like...

    size|placement|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)

  • vmon,

    Will the single column ALWAYS contain the data in the same order?

    --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)

  • Ah... almost forgot... I need to know the name of the table and I need to know some details about what the Primary Key is. The easiest way to do that is to simple provide the CREATE TABLE statement for the table and the Primary Key.

    --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)

  • Thanks for showing the remainder of my post. Here is the table. The field I am struggling with is the last one in table. PersonalizeLine1. I need to parse it into the 3 preceeding columns. The order of the data in the unparsed column should remain the same: Size, Placement, Url. The position of characters may not. The tag or label of the data should stay constant too. Does the unparsed data look like xml? I have not worked with xml so I am not sure.

    Thanks again,

    vmon

    CREATE TABLE [dbo].[tblOrOrder](

    [RecNumId] [int] IDENTITY(1,1) NOT NULL,

    [OrderStatus] [varchar](50) NULL CONSTRAINT [DF_tblOrOrder_OrderStatus] DEFAULT ('Open'),

    [OrderType] [varchar](1) NULL,

    [FirstName] [varchar](50) NULL,

    [LastName] [varchar](50) NULL,

    [Size] [varchar](1) NULL,

    [Placement] [varchar](50) NULL,

    [varchar](50) NULL,

    [PersonalLine1] [varchar](500) NULL,

    CONSTRAINT [PK_tblOrOrder] PRIMARY KEY CLUSTERED

    (

    [RecNumId] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

    ) ON [PRIMARY]

  • Thanks for the table statement. This will be easy and, no, it doesn't matter what it looks like, we don't need to go through the rigors of XML to do this... the Tally table split will work just fine (see the article links I posted).

    I'm on my way to work so can't pick this up for the next 9-10 hours. Take a look at the Tally table splitter in the links and give it a whirl if you can't wait.

    --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)

  • Thanks for replying Jeff. I am not sure how to go about this. The order of data will be constant. The tags will be constant as will the pieces of information. The lenght of the data may change though.

    Can you give me a little direction on how to start this? I am not sure how the Tally Split works.

    Thanks,

    vmon

  • vmon (9/18/2009)


    Thanks for replying Jeff. I am not sure how to go about this. The order of data will be constant. The tags will be constant as will the pieces of information. The lenght of the data may change though.

    Can you give me a little direction on how to start this? I am not sure how the Tally Split works.

    Thanks,

    vmon

    No problem... but I'm at work right now and can't tackle this until I get home tonight.

    --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)

  • Hey Jeff,

    Dont mean to be a bother. Were you able to find a couple minutes to think about how I might approach my challenge? I tried looping by character but it turned into a monster. Just looking for a little more help.

    Thanks,

    vmon

  • Hey,

    Here is an excellent example of how to split strings using static pivoting, I pulled it from here.

    http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/381e4164-f1e0-4b54-828f-2795d2cdcb3e

    Thanks,

    Brad

    CREATE TABLE Foo (

    keycol INT PRIMARY KEY,

    delimited_str VARCHAR(500));

    INSERT INTO Foo VALUES

    (1,

    'size|placement|url');

    WITH Num1 (n) AS (SELECT 1 UNION ALL SELECT 1),

    Num2 (n) AS (SELECT 1 FROM Num1 AS X, Num1 AS Y),

    Num3 (n) AS (SELECT 1 FROM Num2 AS X, Num2 AS Y),

    Num4 (n) AS (SELECT 1 FROM Num3 AS X, Num3 AS Y),

    Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num4),

    Split AS (

    SELECT keycol,

    SUBSTRING('|' + delimited_str + '|', n + 1,

    CHARINDEX('|', '|' + delimited_str + '|', n + 1) - n - 1) AS value,

    n + 1 - LEN(REPLACE(LEFT(delimited_str, n), '|', '')) AS idx

    FROM Nums

    JOIN tbldata

    ON SUBSTRING('|' + delimited_str + '|', n, 1 ) = '|'

    AND n < LEN('|' + delimited_str + '|'))

    SELECT keycol,

    [1] AS Col1,

    [2] AS Col2,

    [3] AS Col3

    FROM Split

    PIVOT

    (MAX(value) FOR idx IN

    ([1], [2], [3])) AS P;

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

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