May 26, 2006 at 7:10 am
I have some records I need to extract data from a comment field. I was thinking of using the substring, but not all data are lined up so the positions are different. Also, some values may or not contain values, but zeroes or spaces. What sql function can I use? Thanks in advance, Rich see below:
Length:1461 Seam:1 Roll:547136-07 Lot Number:547136 Bar Code:4999959765
Length:20 Seam:76 Roll:547136-08 Lot Number:547136 Bar Code:4999959774
Length:0 Seam:22 Roll:547136-09 Lot Number:547136 Bar Code:
May 26, 2006 at 7:15 am
What values do you want from your example, eg to get the Bar Codes
STUFF(comment,1,CHARINDEX('Bar Code:',comment)+8,'')
Far away is close at hand in the images of elsewhere.
Anon.
May 26, 2006 at 9:27 am
I need to extract the Length, Seam, Roll, Lot Number, and Bar Code
from the comment field (History table) and eventually insert this back into the table where these records have been deleted.
If I can just make sure the Length (needs to be 4), and Seam (needs to be 2) are lined up, then I can do the rest.
Length:1461 Seam:1 ---> Length:1461 Seam:01
Length:22 Seam:55 ---> Length:0022 Seam:55
Length:541 Seam:5 ---> Length:0541 Seam:05
Length:0 Seam:15 ---> Length:0000 Seam:15
May 26, 2006 at 10:02 am
STUFF(STUFF(comment,CHARINDEX('Seam:',comment)+5,0,REPLICATE('0',8-(CHARINDEX('Roll:',comment)-CHARINDEX('Seam:',comment)))),8,0,REPLICATE('0',13-CHARINDEX('Seam:',comment)))
Far away is close at hand in the images of elsewhere.
Anon.
May 26, 2006 at 1:32 pm
I am not familiar with STUFF and REPLICATE function, but based on your example, I was able to do the following with TRIM functions:
insert into wrkSeams (DeltaLength,lengthYards,seamNumber,rollID,AFrameID,lotNumber,Barcode)
select
rtrim(ltrim(substring(comments,(charindex('DeltaLegth:',comments)+13),(charindex('Seam Number:',comments)-13)))) as DeltaLength,
substring(rtrim(ltrim(substring(comments,(charindex('DeltaLegth:',comments)+13),(charindex('Seam Number:',comments)-13)))),1,len(rtrim(ltrim(substring(comments,(charindex('DeltaLegth:',comments)+13),(charindex('Seam Number:',comments)-13)))))-1) as lengthYards,
rtrim(ltrim(substring(comments,(charindex('Seam Number:',comments)+12),2))) as seamNumber,
rtrim(ltrim(substring(comments,(charindex('MasterRoll:',comments)+11),9))) as rollID,
rtrim(ltrim(substring(comments,(charindex('AFrameid:',comments)+9),4))) as AframeID,
rtrim(ltrim(substring(comments,(charindex('Lot Number:',comments)+11),6))) as lotNumber,
rtrim(ltrim(substring(comments,(charindex('Bar Code:',comments)+9),10))) as Barcode
from wrkLog
WHERE (CodeFunction = 'InsertSeam')
Thanks again for your help!! Richard
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply