0

I want to select records using LIKE operator with multiple conditions, I can use OR between the conditions but I am looking for a short way to solve this issue.
I am trying the following query:

select * 
from employees 
where name like '%.%' 
   OR name like '%,%' 
   OR name like '%;%' 
   OR name like '%/%' -- this code goes very long  

I am looking for a way to shorten this query.
Any help is appreciated in advance.

1

You can combine the LIKE operator with an array:

select * 
from employees 
where name like any (array['%.%', '%,%', %;%', ...]);

Or a completely different approach, that does not require to repeat the wildcard for each value:

select e.*
from employees e
where exists (select *
              from (values('.', ',', ';')) as t(ch) 
              where e.name like concat('%', t.ch, '%')) 

As you only check for single characters, you could use Postgres' array functions as well: by turning the name into an array of characters and checking for an overlapping array:

select * 
from employees 
where string_to_array(name, null) &&  array['.', ',', ';'];

or

select * 
from employees 
where string_to_array(name, null) &&  string_to_array('.,;', null);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.