Dynamic Like

  • Hi all,

    I am trying to UPDATE the column SupplementaryInformation in the temp table #temptitle3, where the title is like the column val, TEMP Table @textsvalueBG. Val is a series of strings.
    Required rows are is easily returned in regular T-SQL, but the values are stored in a matrix table and so are Dynamic.

    --Simple easy statement
    --Source table WINF with the lookup values using wildcards
    --lookup values are also stored in Matrix table
    --Returns 17770 rows
    SELECT *
    FROM [WINF] w
    WHERE W.title like '%(%' AND WINFkey like 'BG%'
    AND (Title like '%INCIDENTAL%'
    OR Title like '%PART%'
    OR Title like '%PT%'
    OR Title like '%THEME%'
    OR Title like '%CONCERT%'
    OR Title like '%EPS%'
    OR Title like '%SCORE%'
    OR Title like '%DO NOT USE%'
    OR Title like '%CUES%'
    OR Title like '%EMI%'
    OR Title like '%VOCAL%'
    OR Title like '%INSTRUMENTAL%'
    OR Title like '%MIX%'
    OR Title like '%FROM%'
    OR Title like '%END%'
    OR Title like '%CONCERT%'
    OR Title like '%VERSION%'
    OR Title like '%EDIT%'
    OR Title like '%REPRISE%'
    OR Title like '%OVERTURE%'
    OR Title like '%FEAT%'
    OR Title like '%BONUS%'
    OR Title like '%OPUS%'
    OR Title like '%MAIN%'
    OR Title like '%CREDIT%'
    OR Title like '%BUILD@%'
    OR Title like '%FR%'
    OR Title like '%TV%'
    OR Title like '%FILM%'
    OR Title like '%KARAOKE%'
    OR Title like '%PRODUCED%'
    OR Title like '%OS CUES%'
    OR Title like '%O/S PERF%'
    OR Title like '%BACKGROUND%'
    OR Title like '%BKG%' )

    The Dynamic statement is not returning the same number of rows.

    IF OBJECT_ID('tempdb..#temptitle3') IS NOT NULL
                DROP TABLE #temptitle3

    DECLARE @Information3 VARCHAR(MAX)

    SELECT WINFkey,Title,SupplementaryInformation = @Information3
    INTO #temptitle3
    FROM [WINF]
    WHERE title like '%(%' and (WINFkey Like 'BG%' )

    DECLARE @textsvalueBG TABLE(catgeory VARCHAR(2),val VARCHAR(100),SupplementaryInformation VARCHAR(100))
    Insert into @textsvalueBG SELECT category,texts , '' from [rpt].[Matrix] WHERE Category in ('BG')        

    UPDATE #temptitle3
         SET SupplementaryInformation = SUBSTRING(Title,CHARINDEX('(',Title),LEN(Title))
         FROM #temptitle3
         WHERE RTRIM(LTRIM(SUBSTRING(Title,CHARINDEX('(',Title)+1 ,LEN(Title))))
                        IN
                        (select RTRIM(LTRIM(val)) from @textsvalueBG)

    --This only returns 91 rows                    
    SELECT t.*, w.title 'Originial Title'
    FROM #temptitle3 t
    JOIN [WINF] w
    ON t.WINFkey = w.WINFkey
    WHERE SupplementaryInformation is not null

    Any assistance would be appreciated.

  • The second query has an additional WHERE clause that could be affecting the output. Validate that against the first query.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • I think the problem is the IN it needs to be a IN Like from title to %val%. How do I make it a like?


    UPDATE #temptitle3
         SET SupplementaryInformation = SUBSTRING(Title,CHARINDEX('(',Title),LEN(Title))
         FROM #temptitle3
         WHERE RTRIM(LTRIM(SUBSTRING(Title,CHARINDEX('(',Title)+1,LEN(Title))))
                        IN
                        (select RTRIM(LTRIM(val)) from @textsvalueBG)

    SELECT RTRIM(LTRIM(SUBSTRING(Title,CHARINDEX('(',Title)+1,LEN(Title))))
    FROM #temptitle3

    SELECT RTRIM(LTRIM(val)) from @textsvalueBG

    [Title] Search Values:
    GREECE)
    EMI GOLDEN TORCH)
    AIRSHIP DISASTER)
    HOLLAND)
    BATTLE FOR ANZIO)
    LASSIE)

    [VAL] Matrix Value:
    INCIDENTAL

  • I found a way to do the like in the join that appears to be working.

    UPDATE #temptitle3
         SET SupplementaryInformation = SUBSTRING(Title,CHARINDEX('(',Title),LEN(Title))
         FROM #temptitle3
         JOIN @textsvalueBG
         ON RTRIM(LTRIM(SUBSTRING(Title,CHARINDEX('(',Title)+1,LEN(Title)))) LIKE RTRIM(LTRIM(val)) + '%'

  • SELECT column1   FROM table1 AS T1   INNER JOIN Params AS P1    ON T1.column LIKE '%' + P1.param + '%';

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply