In my postgres database 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?
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 real case the matching substrings are given as an array ( e.g. ('794', '43', '17')
) having 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
HINT: No operator found with given name and argument types. You may need to convert types explicitly.
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%';