You are here:

MS SQL Server/Joining on "Similar" records

Advertisement


Question
Jwalant,

Working with SQL 2008. Have 2 tables. Both are a list of all the items 2 different Hospitals purchase. Table A has about 80,000 recordds , table B has 60,000. I want to Join the 2 tables on the description field. However I want to join when they are similar. Such as A = '001_ABC-Bandaid 3x5' B = '1 ABC Bandaid 5x3'.

Any ideas???

Thanks,

Richard

Answer
Hi Richard,
The requirement is a tricky one.

The best approach as per my knowledge would have been to create a catalog table which would be the master table for items such as "ABC Bandaid", and then map its value to Hospital A or B.

I know, its too late to do that now being a massive list you have got.

Well, in your case, is it true that the descriptions will always be 3 part?
E.g.: 001_ABC-Bandaid 3x5
part 1: quantity - 001
part 2: name - ABC-Bandaid
part 3: size - 3x5

You should first find ABC-Bandaid using a separators as underscore or space.
Then, create pure name ABC Bandaid from ABC-Bandaid. You will find code of functions online which can ignore all special characters and replace with space.

And then compare values from A and B.

Please feel free to follow-up.

Regards,

MS SQL Server

All Answers


Answers by Expert:


Ask Experts

Volunteer


Jwalant Natvarlal Soneji

Expertise

From general queries about SQL Severs both 2000 and 2005 to advanced features like Integration Services and Reporting Services.

Experience

Experience in the area: I have been working with database triggers, stored procedures, views, function, joins, dts, Query optimization. Education/Credentials: BE IT - 2005 Batch with First Class.

Publications
http://jwalantsoneji.com

Education/Credentials
BE IT, India

Awards and Honors
MCP

©2016 About.com. All rights reserved.