join with parsing - 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

General SQL Server Forums

New to SQL Server Programming

join with parsing

Author

Topic

sdniec
Starting Member

2 Posts

Posted - 2013-11-26 : 14:51:09

How to accomplish a join when the join condition has to be parsed from another column? I need a list of all the exchange part numbers with exchange part pricing, but need the description from the original part number. Example:

Part Number Description Cost
123-456-1 A ROUND THINGIE 5
150-387-1 EXCH - 123-456-1 2

Needed record is
Part number ExchangePart Description Cost
123-456-1 150-387-1 A ROUND THINGIE 2

Something like

SELECT PART# as exchangepart,

case
when substring(descr, 1,4) = 'EXC-' then trim(substring(DESCR, 5, 25))
when substring(descr, 1,3) = 'EXC-' then trim(substring(DESCR, 4, 26))
when substring(descr, 1,6) = 'EXC - ' then trim(substring(DESCR, 7, 23))
when substring(descr, 1,5) = 'EXCH-' then trim(substring(DESCR,6,24))
when substring(descr, 1,7) = 'EXCH - ' then trim(substring(DESCR,8,22))
when substring(descr, 1,13) = 'EXCHANGE FOR' then trim(substring(DESCR,14,16))
when substring(descr, 1,8) = 'EXCH FOR' then trim(substring(DESCR,9,21))
else DESCR end as parttoreplace,

cost
FROM PRICE as q1

left join
(select part#, DESCR as partdescription
from price) as fDesc
ON fdesc.part# = q1.parttoreplace

WHERE q1.PART# LIKE '150-%'

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2013-11-26 : 16:09:14

Try this:


select q2.[part#] as [Part number]
      ,q1.[part#] as ExchangePart
      ,q2.descr as Description
      ,q1.cost
  from price as q1
       inner join price as q2
               on q1.descr in ('EXC-'         +q2.[part#]
                              ,'EXC'          +q2.[part#]
                              ,'EXC - '       +q2.[part#]
                              ,'EXCH-'        +q2.[part#]
                              ,'EXCH - '      +q2.[part#]
                              ,'EXCHANGE FOR '+q2.[part#]
                              ,'EXCH FOR '    +q2.[part#]
                              ,                q2.[part#]
                              )
 where q1.[part#] like '150-%'

sdniec
Starting Member

2 Posts

Posted - 2013-11-26 : 18:01:27

Excellent answer! Many thank yous.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources