Extract string - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Extract string

Author

Topic

lane0618
Posting Yak Master

134 Posts

Posted - 2003-06-06 : 14:11:14

I have a table called eMRP_ANA:

item desc1 desc2
07532-000 ~01-07532 SCRW,ROHM #8-32 X 1.500
07532-001 SCRW,R O H M,#8-32 X 1 1/2,1040,3
07532-002 SCRW,R O H M,#8-32 X 1 1/2,1040,5
07532-003 SCRW,R O H M,#8-32 X 1 1/2,1040,15A
07532-004 SCRW,R O H M,#8-32 X 1 1/2,1040,26
07532-005 SCRW,ROHM,#8-32X11/2,BLK
07532-006 SCRW,ROHM,#8-32X11/2
07532-007 SCRW,R O H M,#8-32 X 1 1/2,1040,690

I have anaother one called FinsihDef:

finishes
3
5
10B
15
15A
26
26D
L03

I need to determine which items in the eMRP_Ana table go with which finishes in the FinishDef table.

I would like to end up with:

item desc1 desc2 finish
07532-000 ~01-07532 SCRW,ROHM #8-32 X 1.500 null
07532-001 SCRW,R O H M,#8-32 X 1 1/2,1040,3 3
07532-002 SCRW,R O H M,#8-32 X 1 1/2,1040,5 5
07532-003 SCRW,R O H M,#8-32 X 1 1/2,1040,15A 15A
07532-004 SCRW,R O H M,#8-32 X 1 1/2,1040,26 26
07532-005 SCRW,ROHM,#8-32X11/2,BLK null
07532-006 SCRW,ROHM,#8-32X11/2 null
07532-007 SCRW,R O H M,#8-32 X 1 1/2,1040,690 null

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-06-06 : 14:14:40

Could you format the data so that it is easier to read? I can't tell where the columns end. I thought that the commas indicated this, but your data has one record with one comma, another with 4 commas (although there are only 3 columns listed), so please format this so that we can read it. Please provide the CREATE statements for your tables and the INSERT statements for the data so that we can run this easily in Query Analyzer. This is the preferred method when asking for a solution.

Tara

lane0618
Posting Yak Master

134 Posts

Posted - 2003-06-06 : 14:25:26

Oops, forgot to preview... Sorry. Hopefully this is a bit better to read. Neadless to say the data in in really bad shape. I should also mention that the strings I'm looking for can be in either desc1 or desc2.

item----desc1----desc2
07532-000--------~01-07532-------- SCRW,ROHM #8-32 X 1.500
07532-001-------- SCRW,R O H M,#8-32 X--------1 1/2,1040,3
07532-002-------- SCRW,R O H M,#8-32 X--------1 1/2,1040,5
07532-003-------- SCRW,R O H M,#8-32 X--------1 1/2,1040,15A
07532-004-------- SCRW,R O H M,#8-32 X--------1 1/2,1040,26
07532-005-------- SCRW,ROHM,#8-32X11/2,BLK--------
07532-006-------- SCRW,ROHM,#8-32X11/2--------
07532-007-------- SCRW,R O H M,#8-32 X--------1 1/2,1040,690

Edited by - lane0618 on 06/06/2003 14:26:16

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-06-06 : 14:51:25

Let's forget about desc1 for now and just work with desc2.

Is there a way to tell from the record if desc2 contains a finish? I am able to figure it out by looking at it, but there needs to be a consistent way for it to be written in T-SQL. Is it if desc2 contains two commas then it contains a finish and the finish is after the second comma?

Tara

lane0618
Posting Yak Master

134 Posts

Posted - 2003-06-06 : 16:25:08

Good question, but unforntunatly there is no logic behind these descriptions at all and the table has a few hundred thousand records. The finishes could be like:
3 -->(by itself)
% 3%
%,3%
but will never be more than three characters(see finishDef records)

To make a long story short, I don't need these results to be 100% perfect(because I don't know given the state of the data if thats even possible). The results will be used to setup up defaults, the user can select the right one if neccessary. Hope that helps.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources