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

I have a problem when filtering data in a table.

SELECT * 
FROM TABLE_NAME 
WHERE COL1 LIKE '%VALUE%' 
   OR COL2 LIKE '%VALUE%' 
   OR COL3 LIKE '%VALUE%'...;

I want to optimize this SQL statement.

I was thinking about using WHERE IN ... but IN just filters that thing exactly equal to "VALUE".

Any idea for this?

Thanks a lot.

I tried

SELECT * 
FROM TABLE_NAME 
WHERE 'VALUE' IN (COL1, COL2);

but I expected

SELECT * 
FROM TABLE_NAME 
WHERE '%VALUE%' LIKE/IN (COL1,COL2);

Can I do that in PostgreSQL?

CodePudding user response：

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