How to Use SQL Like Condition With Multiple Values in Postgresql

How to use SQL LIKE condition with multiple values in PostgreSQL?

Perhaps using SIMILAR TO would work ?

SELECT * from table WHERE column SIMILAR TO '(AAA|BBB|CCC)%';

SQL - Combining multiple like queries

You can use SIMILAR TO and separate the tags with | pipe '555123%|555321%|555987%'

eg:

SELECT * 
FROM phonenumbers 
WHERE number SIMILAR TO '555123%|555321%|555987%'

SQL select items grouped by multiple values

The typical approach here is to aggregate the rows per instance and use conditional aggregation in the HAVING clause to get only those instances that match your criteria:

select instance
from mytable
group by instance
having count(*) filter (where type = 'A') > 0
   and count(*) filter (where type = 'B') > 0
order by instance;

What I hadn't thought of myself is what Isolated suggests in the request comments: Use INTERSECT, which leads to this very simple query:

select instance from mytable where type = 'A'
intersect
select instance from mytable where type = 'B'
order by instance;

I like both approaches just the same here. My first approach is more versatile, though, as you can easily have various conditions in that query without changing it much. For example if you wanted to limit this to those instances that have types A and B and no other type. You'd just add a condition that the type count must be two or that the count for types other than A and B must be zero.

SQL query that leverages the postgres ILIKE function on multiple values from a table field given a list of IDS

I think something like this should work:

SELECT *
  FROM vehicles
 WHERE model_id IN (SELECT id
                      FROM models
                     WHERE name ILIKE ANY (SELECT name||'%'
                                             FROM models
                                            WHERE id IN (<array of model IDs>)
                                          )
                   );

Disclosure: I am an EnterpriseDB (EDB) employee

SQL: Multiple Column in a Table has same a condition: WHERE COL1 LIKE 'VALUE' OR COL2 LIKE 'VALUE'

To answer your direct question, you could concatenate the columns and use an array if there's multiple values you want to search for.

create table t1 (
  col1 text
  ,col2 text
  ,col3 text
);

insert into t1 (col1,col2,col3)
values
('foobar', 'bar', 'alpaca')
,('cat','dog','duck');

SELECT 
    *
FROM t1
WHERE (col1 ||' '|| col2 ||' '|| col3) ILIKE ANY(ARRAY['%foo%','%bar%', '%cat%'])

https://dbfiddle.uk/?rdbms=postgres_14&fiddle=e54e5eecbdc4c1374d441d6553049fbf

Unsure how performant this would be, but I believe it answers your direct question and is probably more readable than a long list of OR statements.

Postgresql - multiple select condition

You may try the following:

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY "type" ORDER BY updated_at DESC) rn
    FROM yourTable
)

SELECT id, author_id, name, "type", created_at, updated_at
FROM cte
WHERE
    ("type" IN ('A', 'B') AND rn = 1) OR
    "type" NOT IN ('A', 'B');

This approach uses ROW_NUMBER to find the latest rows for all types. In the query on the CTE, we select only the most recently updated rows for types A and B, but we select all rows for all other types.

Postgres array lookup multiple values in where clause

demo:db<>fiddle

You can use the overlap operator && for arrays:

select * from stud 
where array[8,9] && subject_id

How to use ILIKE for multiple values in single column in rails?

Use:

Project.where("name ILIKE ANY (array[?])", ["%Arvind Oasis%", "%Prestige Jindal City%", "%XXXX%"])

---

Related Topics