September 17, 2009 at 9:58 am
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
I am not sure how to approach this. Any suggestions?
thanks,
vmon
September 17, 2009 at 10:20 am
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.
September 17, 2009 at 10:11 pm
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
Change is inevitable... Change for the better is not.
September 17, 2009 at 10:12 pm
vmon,
Will the single column ALWAYS contain the data in the same order?
--Jeff Moden
Change is inevitable... Change for the better is not.
September 17, 2009 at 10:14 pm
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
Change is inevitable... Change for the better is not.
September 18, 2009 at 5:57 am
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,
[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]
September 18, 2009 at 6:36 am
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
Change is inevitable... Change for the better is not.
September 18, 2009 at 11:06 am
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
September 18, 2009 at 12:20 pm
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
Change is inevitable... Change for the better is not.
September 21, 2009 at 7:01 am
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
September 21, 2009 at 8:14 am
Hey,
Here is an excellent example of how to split strings using static pivoting, I pulled it from here.
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