0

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?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
quyetkaito
  • 13
  • 3
  • 1
    I'd rather consider a table redesign. Why do you have to search several columns? – jarlh Apr 05 '22 at 16:18
  • 1
    What do you mean by "optimize"? Is your issue performance or just the verbosity of the query? – Stephan Apr 05 '22 at 16:19
  • Does this answer your question? [Is there a combination of "LIKE" and "IN" in SQL?](https://stackoverflow.com/questions/3014940/is-there-a-combination-of-like-and-in-in-sql) – Crick3t Apr 05 '22 at 16:21
  • For "optimizing" I think you really mean to use fulltext search capabilities instead (check chapter 12 in documentation). – Cetin Basoz Apr 05 '22 at 16:22
  • 1
    @Stephan In this case, I think "Just the verbosity of the query", lol – quyetkaito Apr 05 '22 at 16:34
  • Then I think there is nothing to be optimized for you, maybe just replace Like with ILike or use regular expression (~). – Cetin Basoz Apr 05 '22 at 16:49

1 Answers1

1

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.

Edward Radcliffe
  • 537
  • 2
  • 11
  • Valid, you would need to add a space between the concatenation of the columns. I've updated my answer. – Edward Radcliffe Apr 05 '22 at 16:58
  • @CetinBasoz thank you so much, I think this is exactly what i want. Best regards – quyetkaito Apr 05 '22 at 17:02
  • @quyetkaito, I didn't do that, Edward Radcliffe did. Please mark it as an answer (and I still suggest you to look into chapter 12:) – Cetin Basoz Apr 05 '22 at 17:03
  • @CetinBasoz okay – quyetkaito Apr 05 '22 at 17:04
  • 1
    FYI this could lead to unexpected results if the search term has spaces. Example, if col1 has "Hello" and col2 has "World" and the user searches "Hello World", your original query would return nothing. But this query would return that record. To protect against this, probably safer to delimit with an unusual character like pipe "|" or tilde "~" – Stephan Apr 06 '22 at 13:27