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%'


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'
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:

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)
('foobar', 'bar', 'alpaca')

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

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 (
FROM yourTable

SELECT id, author_id, name, "type", created_at, updated_at
FROM cte
("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


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?


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

Related Topics

Leave a reply