parsing a field to join

[Terris]

OK so I'm drawing a blank on this one. I have a field in one table that is Order# ' ' line# + release#. Sometimes the order # is 5 characters, sometimes 6. Line# can be 1 to 6 digits, release# does not always exist but when it does it is 3 characters. I need to extract the line# to use in the join statement.
Sample data

000835 2 = order# 000835 line# 2
001024 3000 = order# 001024 line# 3 release# 000
T9342 2 = order# T9342 line# 2
T9276 5000 = order# T9276 line# 5 release#000

I need to join
from table1 join table2 on table1.fcmaster = table2.fsono and table1.line# = table2.finumber

any ideas on how to parse the data out so I can use it in the join?

 

[swampBoogie]

A bit curios about some of the values, e.g.

T9276 5000

How do you know that the line# is 5 and not 5000?


I should change that design as soon as possible and store each item in a separate column. You will never get any efficency as the joins will not be able to use any indexes.

 

[NoCoolHandle]

There are two functions that would be usefull here.

Substring
and
CHarindex

in your case you would do something like

substring([line#],1,charindex(' ',[line#]))

HTH

Rob

 

[SQLSister]

Have to agree with swamp boogie, the design of the table is horrible. If you are importing this data from somewhere else and then parsing it into a table of your own, then doing what you suggest is fine. Otherwise you need to redesign your system or it will be very slow as these types of queries cannot effectively use the indexes. Basic database design rule number one - only store one piece of information in a field! Especially if you plan to search on it!! This is one of those cases where it doesn't matter how much work you have done or how much will need to be redone, the design is just not workable in the long run, so the sooner you fix it the better.

 

[Terris]

If I could only redesign, not an option I am reporting off an existing database that drives our ERP system. This is an audit table, the table name is stored in one field the table key in another. I am using this audit table to pull out changes to sales orders for a booking report).
if the table field is SORELS then I know that the 000 will be there and will always be 000
if the table field is SOITEM then the 000 will not be there. I need to link to the SOITEM table to pull either qty or price soitem table unique join will be the line number. I have gotten to the point where I have 10000 or 1 but am stumbling at pulling off the 000 when they exist here is what I am using so far
Substring(fcprimekey,(CHARINDEX(' ',fcprimekey)+1),6)