SQL "NOT LIKE" reduce as many NOT LIKE as you can in a single line

user_16759218 0
Viewed 247

I have a query where I ask it to bring me information except for a specific one.

Is there a way to reduce the number of NOT LIKE and include it all in the same pattern?

PD: I'm using Redshift AWS.

SELECT  distinct action_url 
from event_public
WHERE action_url like '%int/about/%/offices%' 
and action_url not LIKE '%russia%'
and action_url not LIKE '%japan%' 
and action_url not LIKE '%brazil%' 
and action_url not LIKE '%china%' 
and action_url not LIKE '%singapore%' 
and action_url not LIKE '%nigeria%' 
and action_url not LIKE '%algeria%'
and event_action ='Pageview'
amazon-redshift sql
2 Answers

Yes there is. You can use REGEXP

SELECT  distinct action_url
from event_public 
WHERE action_url like '%int/about/%/offices%'
and action_url not REGEXP 'russia|japan|brazil|china|singapore|nigeria|algeria'
and event_action ='Pageview' ;

Edit: For heavy duty matching, don’t use REGEXP() if LIKE can do the job. In particular if a lot of rows can be ruled out by virtue of the first characters in the string.

user_164619520

You could use the "~" POSIX regexp operator.

But the resulting regular expression would be complex and eat up lots of cpu. See redshift docs

You are probably better of with the simpler but verbose list of likes.

user_382070
Related