HELP Parsing a field

Last Post 20 Mar 2008 12:03 PM by Headstrong. 6 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
Headstrong
New Member
New Member

--
16 Mar 2008 07:24 PM
Hi Folks
Hope you can help me with this.
I have a field called "Parts" this field holds information about parts such as quantity, part#, description and so on
within the field "Parts" each item is separated by a comma and each part is separated by a carriage return
I need a query that will extract the first 3 items for each part...
I'm guessing I probably need a function or store procedure to do this right?

for example, the parts field has the data below

1,BEED,PigTail,$60,$60,$,$0
2,CE-24,Clamp,$1,$2,$,$0

I need the query to extracts this:

Qty Part# Description
1 BEED Pigtail
2 CE-24 Clamp


Thanks in advance for any help
using SQL2000
Headstrong
New Member
New Member

--
17 Mar 2008 09:40 AM
I know what you mean about changing the schema but unfortunately their front end custom software requires it this way, I just need the data for something else... I will research the SUBSTRING and CHARINDEX
SQLUSA
New Member
New Member

--
17 Mar 2008 10:11 PM
Yes.

Typically function is used for parsing CS lists.

Check this out: http://www.sqlusa.com/bestpractices/complexcharindex/" target="_blank" rel="nofollow">http://www.sqlusa.com/bestpractices...charindex/

Or this one for function implementation: http://www.sqlusa.com/bestpractices2005/listparsing/" target="_blank" rel="nofollow">http://www.sqlusa.com/bestpractices...stparsing/


Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
SQLUSA: http://www.sqlusa.com/ SQL 2005 BI Workshop NYC - April 21-24! SSAS, SSIS, SSRS
Headstrong
New Member
New Member

--
18 Mar 2008 10:45 AM
Thanks SQLUSA

I almost have it but I'm stuck
here is the data in the parts field
returned as Qty,PartNumber, Description

1, 5160, LCD Keypad,$30,$0,$45

but I'm getting
qty = 1
part = 5160
description = ,LCD

SELECT LEFT(parts, CHARINDEX(',', parts) - 1) AS Qty,
SUBSTRING(parts, CHARINDEX(',', parts) + 1, CHARINDEX(',', parts, CHARINDEX(',', parts) + 1) - (CHARINDEX(',', parts) + 1)) AS PartNumber,
SUBSTRING(parts, CHARINDEX(',', parts,CHARINDEX(',', parts,CHARINDEX(',', parts) + 1) ), CHARINDEX(',', parts, CHARINDEX(',', parts) + 1) - (CHARINDEX(',', parts) + 1)) AS Description
FROM Purchase

thanks for your help
SQLUSA
New Member
New Member

--
19 Mar 2008 10:55 PM
Can you try this?

Let us know it it works for you.

declare @parts varchar(512)
set @parts = '1, 5160, LCD Keypad,$30,$0,$45'



SELECT LEFT(@parts, CHARINDEX(',', @parts) - 1) AS Qty,
SUBSTRING(@parts, CHARINDEX(',', @parts) + 1, CHARINDEX(',', @parts, CHARINDEX(',', @parts) + 1) - (CHARINDEX(',', @parts) + 1)) AS PartNumber,
SUBSTRING(@parts, CHARINDEX(',', @parts,CHARINDEX(',', @parts,CHARINDEX(',', @parts) + 1) )+1, CHARINDEX(',', @parts, CHARINDEX(',', @parts) + 1) - (CHARINDEX(',', @parts) + 1)) AS Description


Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
BI Workshop NYC SSAS, SSIS, SSRS - April 21-24: http://www.sqlusa.com/
Headstrong
New Member
New Member

--
20 Mar 2008 12:03 PM
Thanks for your help guys...

it works and thanks to Pro Petes posting it does not cut the desription anymore
Now I just have to loop thru the parts field for a carriage return to get each individual line/part

thanks again

nosepicker
New Member
New Member

--
20 Mar 2008 01:58 PM
For anything beyond the first three columns, you can see that this will quickly get messy. I usually prefer to export this data to a text file and then import it to a staging table (using bcp or dts/ssis), using the commas as column delimiters. Then you can use whichever columns you want.
You are not authorized to post a reply.

Acceptable Use Policy