Parse data colunm into 3 columns

Question

Parse data colunm into 3 columns

vmon

SSChasing Mays

Points: 624
More actions
September 17, 2009 at 9:58 am

#156231

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

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

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

LutzM SSC Guru Points: 107049 More actions · Answer 1

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]

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 2

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)

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 3

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)

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 4

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)

vmon SSChasing Mays Points: 624 More actions · Answer 5

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]

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 6

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)

vmon SSChasing Mays Points: 624 More actions · Answer 7

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

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 8

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)

vmon SSChasing Mays Points: 624 More actions · Answer 9

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

Bradley Jacques Ten Centuries Points: 1301 More actions · Answer 10

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;