Trying to see if I can build some logic around matching these columns. I have 2 tables
Table A
Make Model
BMW 2 Series
BMW 3 Series
BMW 3 Series GT
BMW 5 Series
BMW 5 Series GT
BMW 7 Series
Rolls-Royce Other
Mini Cooper
Mini Coupe
Table B
Make Series
BMW 2 Series
BMW 3 Series
BMW 5 Series
Bavarian Motor 7 Series
Rolls-Royce 42000 GT
Mini Cooper
I need extract the output as below and store in a table
Output
Make Model_A Model_B
BMW 2 Series 2 Series
BMW 3 Series 3 Series
BMW 3 Series GT 3 Series
BMW 5 Series 5 Series
BMW 5 Series GT 5 Series
BMW 7 Series 7 Series
Rolls-Royce Other 42000 GT
Mini Cooper Cooper
Mini Coupe Cooper
For the most part, I could match the columns using the query below:
( A.SERIES like '%' + B.model + '%'
OR B.model like '%' + A.series + '%' )
But this does not handle the "Coupe" vs "Cooper", Rolls-Royce nor the "BMW" vs "Bavarian Motor" cases.
Any idea how I can handle all these in T-sql or a query? Soundex nor any distance algorithm works in this instance - tried both.
I know I can handle all cases using a "union all" to run in a single query, but the key is to handle the above edge cases.
Thanks in advance for your help. ~Bee
Aucun commentaire:
Enregistrer un commentaire