How to fix data discrepancies between columns from two tables The problem:
I am working for a retail site that sells various products for cars and trucks. We want to integrate eBay and Amazon API to list our products on these stores. The problem arises when I try to send Vehicle compatibility data.

Example from our DB:

Product ID | Other IDs | Make  | Model
  *number* | *number*  | Ford  | F150 Crew Cab
  *number* | *number*  | Chevy | Silverado 2500/3500
  *number* | *number*  | Dodge | Ram Pickup 2500-5500 HD


Example from eBay Master Vehicle List DB:

 Make      | Model
 Ford      | F-150
 Chevrolet | Silverado 2500
 Dodge     | Ram 2500


As seen from the examples eBay/Amazon doesn't recognize our Models. There is no easy solution like using a "split" function to split Models into Model and Sub-model for example, because every case is different. Sometimes Model fields have Submodel as well, sometimes they have a range of Submodels (2500-5500), sometimes Model is not correct (F150 instead of F-150) etc.

Solution:
Changing every Model in the database to a correct Model format is not an option, because our system already uses these incorrect Models for business logic.

Writing a million exceptions in the code to handle incorrect Models is not very appealing as well.

The only solution I see is to create a new table and fill it manually with correct model names and their corresponding IDs for our products. This would be very time consuming task though.

Could anyone suggest a good solution for this problem?
With extensive experience consulting in the automotive sector, eBay is likely using real models as they come from OEM. You're conflating models and trims in your own system.

F150 Crew Cab            -- Crew cab is a trim level.
Silverado 2500/3500      -- Likely two models here.
Ram Pickup 2500-5500 HD  -- likely two models here too, with HD as a trim.


You need to figure out what is a model and what is a trim. Or, use a third party data provider like Chromedata which provides styles that often determined by model and trim. If eBay provides you a list of models, you need to map those into your local data source. Usually you'll have like 60%-direct match, 20%-inferrable with accuracy, 20%-requires human Q/A.

This is just part of how to integrate with other systems.

Looking at the new eBay guide I can give you an idea of the workload if that's not an option (which I may try first because it's probably all in a day's work).

From the eBay motors listing:


This listing of category ids needs to be loaded into a hierarchical database., alternatively and better use the api
Each entry needs to be weighed with the parents. and flattened.
Collapse it into a full text search vector.


Local database


In your own database, select all of the distinct "models" and make sure that they all have the same make.
Create a tsquery of the make, model
Run this against the eBay motors database above.