Hi have two tables of drug listings and I need to join them together on their names (GENERIC_NAME and DRUG_NAME) and the only issue is that the naming is not precise.
For instance what may have a DRUG_NAME of "Brentuximab Vedotin" might have a GENERIC_NAME of "Brentuximab" and
vice versa. Despite these differences I do want them to match. If 'how much' matching is a question then I'd say at the very least the first word should match, if it can be smarter than that I'd say the first 6 characters.
SELECT P.GENERIC_NAME, HD.DRUG_NAME FROM StagingPharmacy.DBO.PDM P RIGHT JOIN HAZARDOUS_DRUGS HD
ON P.GENERIC_NAME = HD.DRUG_NAME
How can I accommodate for this?