MS SQL Server/Joining on "Similar" records
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'.
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.