Question 185039 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – postgres. How to check if a field contains at least one substring form 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.

Tags:

4

Answers


  1. 0

    You could cast the code field to text and then use LIKE:

    SELECT *
FROM yourTable
WHERE code::text LIKE '%794%' OR code::text LIKE '%43%' OR code::text LIKE '%17%';

    If you have many substrings and want to avoid a lengthy WHERE clause, then load your substrings into a proper table:

    Table: substrings
code
794
43
17

    And use the following join with your original table to find matches:

    SELECT DISTINCT *
FROM yourTable t1
INNER JOIN substrings t2
    ON t1.code::text LIKE '%' || t2.code || '%';
    Login or Signup to reply.
  2. 0

    You can use the SQL "LIKE" operator combined with the "OR" operator to extract the records that contain any of the specified substrings in the "code" field.
    In your example:

    SELECT *
FROM *your_table*
WHERE code LIKE '%794%' OR code LIKE '%43%' OR code LIKE '%17%';

    To know more about "LIKE" operator I recommend to look at some other example here: SQL LIKE Operator

    Login or Signup to reply.
  3. 0

    To avoid repeating the where condition for each substring, you may use a cte that contains all the substrings and then do a join with your table as the following:

    with matching_list as
(
  select s from
  (values('794'),('43'), ('17')) as t(s)
)
select t.* 
from table_name t join matching_list mch
on strpos(t.code::text, mch.s)>0 -- or (on t.code::text like concat('%', mch.s, '%'))

    demo

    Login or Signup to reply.
  4. 0

    You can use unnest() on your array for the input an map the content using a regex:

    SELECT mytable.* 
FROM mytable
    JOIN unnest('{"794", "43", "17"}'::text[]) AS list(content) 
        ON code ~ content; -- regex
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search