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
How to Select an Empty Result Set
Sql Server Pass Column Name as Where Clause Parameter
Extracting Data Between Two Delimiters in SQL Server
Access Denied for Load Data Infile in MySQL
How to Select Multiple Values in One Field MySQL
Formatting Numbers by Padding With Leading Zeros in SQL Server
Add a Column to Specific Position in Mssql Server
Want to Run Multiple SQL Script File in One Go With in Sqlplus
Compare 2 Column Values in Same Table
Simple Check for Select Query Empty Result
How to Get Only Digits from String in MySQL
Is There a Command to Test an SQL Query Without Executing It ( MySQL or Ansi SQL )
Query to Calculate Average of Employee Salaries Working Under Manager in SQL
How to Import CSV Data into a Table Without Knowing the Columns of the Csv
Postgresql Error: Fatal: Role "Username" Does Not Exist
Node.Js Mssql Tedius Connectionerror: Failed to Connect to Localhost:1433 - Connect Econnrefused
Error 1265. Data Truncated for Column When Trying to Load Data from Txt File
Update Multiple Rows in a Table from Another Table When Condition Exists