How to avoid tons of lower(column) in WHERE clause for several LIKE comparisons?

Question

I've got to filter data in a string column and thus need to compare the column using NOT LIKE with a bunch of strings.

I'm using SQL Server and my code looks like this:

SELECT history.date,
       history.new_value,
       res.resource_name
FROM   item_history history, 
       owned_resource   res
WHERE  (history.attr_name = 'resource_contact' OR history.attr_name = 'Kontakt-UUID')
       AND res.inactive = 0 
       AND history.com_par_id = res.own_resource_uuid
       AND lower(history.new_value) NOT LIKE '%tp für%'
       AND lower(history.new_value) NOT LIKE '%rücklieferung%'
       AND lower(history.new_value) NOT LIKE '%rückläufer%'
       AND lower(history.new_value) NOT LIKE '%stoerreserve%'
       AND lower(history.new_value) NOT LIKE '%zentrallager%'
       AND lower(history.new_value) NOT LIKE '%bhs-pool%'
       AND lower(history.new_value) NOT LIKE '%lager halle%'        
       AND lower(history.new_value) NOT LIKE '%lager logistik%'
       AND lower(history.new_value) NOT LIKE 'reserve %'
       AND lower(history.new_value) NOT LIKE '%igeko%bhs%'
       AND lower(history.new_value) NOT LIKE '%service%ecg%'
       AND lower(history.new_value) NOT LIKE '%multifunktionsdrucker%'
       AND lower(history.new_value) NOT LIKE 'nn%gisa%raum%'
       AND lower(history.new_value) NOT LIKE '%citrix%admins%'
       AND lower(history.new_value) NOT LIKE '%personalwesen%'
       AND lower(history.new_value) NOT LIKE '%etagendrucker%'
       AND lower(history.new_value) NOT LIKE '%schulungsraum%'
       AND lower(history.new_value) NOT LIKE '%team%raum%'
       AND lower(history.new_value) NOT LIKE  '%beratungsraum%'
       AND lower(history.new_value) != 'reserve'
)

I guess the performance is not the best calling "lower()" over and over. Also, as a programmer, my nails are rolling up seeing so much redundant code. Unfortunately I did not find a nice way to use a variable or something. (I want to add that I cannot simply add a new computed column, which would be a good way here, I'm only authorized to read data.)

Any advise to make the code smarter would be appreciable?

Answer 1

I would use join here instead of old writing way
Regarding "lower" are you sure that this is needed?
Did you check collation of new_value?
If it has CI(case insensitive) in collation name it you can avoid using lower.

If you do, you can write something like this

WITH cte
    AS ( SELECT history.date
               ,history.new_value
               ,res.resource_name
               ,LOWER(history.new_value) AS new_value
         FROM   item_history history
         JOIN   owned_resource res
                ON (   history.attr_name = 'resource_contact'
                       OR history.attr_name = 'Kontakt-UUID' )
                   AND res.inactive = 0
                   AND history.com_par_id = res.own_resource_uuid )
SELECT *
FROM   cte
WHERE  new_value NOT LIKE '%tp für%'
       AND new_value NOT LIKE '%rücklieferung%'
       AND new_value NOT LIKE '%rückläufer%'
       AND new_value NOT LIKE '%stoerreserve%'
       AND new_value NOT LIKE '%zentrallager%'
       AND new_value NOT LIKE '%bhs-pool%'
       AND new_value NOT LIKE '%lager halle%'
       AND new_value NOT LIKE '%lager logistik%'
       AND new_value NOT LIKE 'reserve %'
       AND new_value NOT LIKE '%igeko%bhs%'
       AND new_value NOT LIKE '%service%ecg%'
       AND new_value NOT LIKE '%multifunktionsdrucker%'
       AND new_value NOT LIKE 'nn%gisa%raum%'
       AND new_value NOT LIKE '%citrix%admins%'
       AND new_value NOT LIKE '%personalwesen%'
       AND new_value NOT LIKE '%etagendrucker%'
       AND new_value NOT LIKE '%schulungsraum%'
       AND new_value NOT LIKE '%team%raum%'
       AND new_value NOT LIKE '%beratungsraum%'
       AND new_value != 'reserve';

Answer 2

For the sake of completeness, you could have solved the problem (many excludes) on the following way too:

SELECT history.date
     , history.new_value
     , res.resource_name
  FROM item_history        AS history
 INNER JOIN owned_resource AS res
    ON history.com_par_id = res.own_resource_uuid
   AND res.inactive       = 0
 WHERE (history.attr_name = 'resource_contact' OR history.attr_name = 'Kontakt-UUID')
   AND NOT EXISTS
     (SELECT *
        FROM (VALUES ('%tp für%')
                   , ('%rücklieferung%')
                   , ('%rückläufer%')
                   , ('%stoerreserve%')
                   , ('%zentrallager%')
                   , ('%bhs-pool%')
                   , ('%lager halle%')
                   , ('%lager logistik%')
                   , ('reserve %')
                   , ('%igeko%bhs%')
                   , ('%service%ecg%')
                   , ('%multifunktionsdrucker%')
                   , ('nn%gisa%raum%')
                   , ('%citrix%admins%')
                   , ('%personalwesen%')
                   , ('%etagendrucker%')
                   , ('%schulungsraum%')
                   , ('%team%raum%')
                   , ('%beratungsraum%')
                   , ('reserve')) AS ex (exclude)
       WHERE history.new_value LIKE ex.exclude);

PS: I don't like the old oracle-school and non-ANSI-standard comma style JOINs...

Answer 3

Based in my experience, using SQL functions in WHERE conditions is bad for the SQL performance.

You can rewrite your script to do the following:

1. Create a temp table or variable table
2. INSERT INTO SELECT to that temp or variable table but in the SELECT use the lower() to the column that you are currently using in WHERE condition.
3. SELECT [columns] FROM the temp or variable table.