Search code examples
sqlstringpostgresqlsubstring

postgres. How to check if a field contains at least one substring from a list of substings?

I have a table like this

 one | two | three   |  code   | four 

-----+-------+-------+---------+--------

   0 | 21750 | 10110 | 1016916 |   20.0

   0 | 21750 | 10111 | 1017949 |   20.2 

   0 | 21750 | 10115 | 101792 |   21.0 

   0 | 21737 | 10110 | 100753 |   20.0  

   0 | 21737 | 10110 | 14343054 |   20.0

I want to extract all records in which the field code contains at least one of the following substrings:

794, 43, 17

How can I do it?

UPDATE

I know I could achieve this by writing

select * from mytable where
code ilike '%794%' or
code ilike '%43%' or
code ilike '%17%';

but imagine that in the true case the matching substrings are given to my with the shape of an array ( e.g. ('794', '43', '17') ) which would have length > 100, so I don't want to rewrite this array into 100+ WHERE conditions, each one for each substring to be searched in the code field.

I have tryed with

select * from mytable where code && '{"794", "43", "17"}';

but it throws this error

enter image description here

HINT: No operator found with given name and argument types. You may need to convert types explicitly.

Solution

  • I found another solution, simple and beautiful, which I like more than any other one:

    select * from mytable where
code  ~* '(794|43|17)' ;

    where the ~* operator in Postgres does case insensitive regex matching (see an example here).

    That query is equivalent to

    select * from mytable where
code ilike '%794%' or
code ilike '%43%' or
code ilike '%17%';