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

closed as unclear what you're asking by Max Vernon, kevinsky, mustaccio, hot2use, Marcello Miorelli Jun 6 at 8:16

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

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;

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