• 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)


    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].


    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]