当前位置: 动力学知识库 > 问答 > 编程问答 >

sql - Comparing records from different tables with slightly different data

问题描述:

I have two tables. Each table has product information and a price. I would like to be able to get results which show the product name and both prices.

However, the product name in each table is written slightly differently. e.g.

Table 1

Name Price Pack Size

Aciclovir 200 mg Tablets 3.50 25

Aciclovir 400 mg Tablets 4.20 56

Aciclovir 800 mg Tablets 5.40 35

ACICLOVIR CREAM 2.40 GM

Table 2

ACICLOVIR 200MG TABs 1 25

ACICLOVIR 200MG TABs 1 25

ACICLOVIR 400MG TABs 2 56

Aciclovir 5% Cream 2gm 2.30 na

I've tried with LIKE, but I'm not really getting the results that I need.

网友答案:

In this case (and in SQL Server), I can suggest you to use this:

REPLACE(a.Name, ' ', '') LIKE REPLACE(REPLACE(b.Name, ' ' ,''), 'TABs', 'TAB%s')
OR 
REPLACE(b.Name, ' ' ,'') LIKE REPLACE(REPLACE(a.Name, ' ', ''), 'CREAM', '%CREAM%')

I can simply tell you when you have something that is changing remove it from your compare when you want to ignore a ' ' remove it and so on. when a MG is changing you can also remove it in your compare, I remove this ignore texts by using REPLACE().

网友答案:

Use trim where ever possible to remove spaces. This will help you to make the column value similar.

For Example- 800 mg and 800MG .. by using trim you can get this same value then use them in your query

分享给朋友:
您可能感兴趣的文章:
随机阅读: