r_noob (7/15/2013)
... ON (A.Field1 like '% ' + B.LookupValue + ' %')
That looks like words in a sentence. Try Jeff's splitter: split the sentence on space, remove any leading blanks and seek into your lookup table.
Like this:
SELECT *
FROM #Table1 a
CROSS APPLY dbo.DelimitedSplit8K(a.Field1, ' ') split
WHERE EXISTS (SELECT 1 FROM #Lookup b
WHERE b.LookDesc = split.Item)
SELECT *
FROM #Table1 a
CROSS APPLY dbo.DelimitedSplit8K(a.Field1, ' ') split
INNER LOOP JOIN #Lookup b
ON b.LookDesc = split.Item
Prep:
IF OBJECT_ID('tempdb..#Table1') IS NOT NULL
DROP TABLE #Table1
CREATE TABLE #Table1 (
[ID] INT IDENTITY(1,1) NOT NULL,
[Field1] VARCHAR(100),
[Field2] VARCHAR(3) NULL)
INSERT INTO #Table1
SELECT 'The quick brown fox','No' UNION ALL
SELECT 'jumped over','No' UNION ALL
SELECT 'the','No' UNION ALL
SELECT 'lazy dog','No' UNION ALL
SELECT 'One day in the life of Ivan Denisovitch', 'No'
INSERT INTO #Table1 select a.Field1, 'No' AS Field2 from #Table1 a, sys.columns b
CREATE UNIQUE CLUSTERED INDEX ucx_ID ON #Table1 (ID)
IF OBJECT_ID('tempdb..#Lookup') IS NOT NULL
DROP TABLE #Lookup
CREATE TABLE #Lookup ([LookDesc] VARCHAR(30) NOT NULL)
INSERT INTO #Lookup
SELECT 'dog' UNION ALL
SELECT 'fox' UNION ALL
SELECT TOP 45000 x = RIGHT(NEWID(),5)+RIGHT(NEWID(),5) FROM SYSCOLUMNS A, SYSCOLUMNS B
CREATE UNIQUE CLUSTERED INDEX ucx_LookDesc ON #Lookup (LookDesc)
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]