Skip to content
Advertisement

Improve SQL: NOT LIKE being repeated 3 times to filter out 3 different strings

I am new at SQL so any help is appreciated. How can I improve on the following conditions in my where clause:

AND Upper(customer_name) NOT LIKE Upper('%Demo%')
AND Upper(customer_name) NOT LIKE Upper('%Demo Test System%')
AND Upper(customer_name) NOT LIKE Upper('%Practice%') 

Is there a way I can do this in one line or is there a better way of doing the same.

Advertisement

Answer

Convert the string literals to upper-case and get rid of the second line as it is already covered by the first line.

AND Upper(customer_name) NOT LIKE '%DEMO%'
AND Upper(customer_name) NOT LIKE '%PRACTICE%'

Is there a way I can do this in one line

Remove the line break:

AND Upper(customer_name) NOT LIKE '%DEMO%' AND Upper(customer_name) NOT LIKE '%PRACTICE%'

Or use regular expressions (but they are much slower than ordinary string functions):

AND NOT REGEXP_LIKE(customer_name, 'DEMO|PRACTICE', 'i')
User contributions licensed under: CC BY-SA
5 People found this is helpful
Advertisement