0

I have a drugs catalogue table which is regularly updated. I need to see whether these drug names (e.g. ‘Paracetamol 500mg’) are referred to anywhere within a lengthy text field (up to nvarchar (2000) characters long) in another table called Logic. There are no discernible joins or IDs between these two tables. There are about 1500 drug names which regularly need to be checked against the text in the Logic table. I have tried everything from fuzzy joins to soundex but am not getting the required results

select drugname
from #drugs t1
where exists (select logic from #logic t2
              where t1.drugname <> t2.logic
                and SOUNDEX(t1.drugname) =
                    SOUNDEX(t2.logic))

Is there a simple way to do this? Thanks in advance

1

Based on your comments

It needs to run with this logic "Does drugname 1 exist anywhere within the Logic rows? If so, return which row (it's location within is not important) Then repeat the same logic with drugname 2 etc etc. Thanks

&

The Soundex is not working. I don't think it is suitable in what I am trying to do. I just need a query to search each drugname within the dataset to see if it exists anywhere in the logic table. Thanks

You could use CROSS APPLY + LIKE to only get the drugname's that have a match with the logic column. It won't be sargable but should get the job done. If you need to add the logic to identify the row, multiple of the same drugname's could be returned.

Query

SELECT drugname,A.logic
FROM #drugs t1
CROSS APPLY
(
SELECT logic 
FROM #logic t2
WHERE t1.drugname <> t2.logic
AND t2.logic LIKE '%' + t1.drugname + '%'
)
AS A;

Test data

CREATE TABLE #drugs(id int identity(1,1) PRIMARY KEY NOT NULL,
                    drugname varchar(20));
INSERT INTO #drugs(drugname)
VALUES('Paracetamol 500mg'),('Paracetamol 1000mg');

CREATE TABLE #logic(id int identity(1,1) PRIMARY KEY NOT NULL,
                    logic varchar(max));
INSERT INTO #logic
VALUES('BlablaParacetamol 500mg'),('__Paracetamol 1000mg__'),('...');

Result

drugname    logic
Paracetamol 500mg   BlablaParacetamol 500mg
Paracetamol 1000mg  __Paracetamol 1000mg__

If you don't care about the logic column and only want one row for each drugname that has one or more matches.

SELECT drugname
FROM #drugs t1
CROSS APPLY
(
SELECT TOP(1) logic 
FROM #logic t2
WHERE t1.drugname <> t2.logic
AND t2.logic LIKE '%' + t1.drugname + '%'
)
AS A;
| improve this answer | |

Not the answer you're looking for? Browse other questions tagged or ask your own question.