Fuzzy searching? Any Alternatives?
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


&amp;


  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 &lt;&gt; 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 &lt;&gt; t2.logic
AND t2.logic LIKE '%' + t1.drugname + '%'
)
AS A;