CODEWITHSUNDEEP
sqlpostgresqlsql-like
David Sun
David Sun

Reputation: 87

sql, strategies to find out string contains certain texts

I want to select any data that contains 800, 805, 888... (there are 8 pattern texts) in the column.

Do I have to use like statement 8 times for each one or is there a faster way?

Example:

SELECT * FROM caller, 

WHERE id LIKE '%805%' OR id LIKE'%800' OR ... ;

(PS. I am not allowed to create another table, just using sql queries.)

Upvotes: 3

Views: 49

Answers (2)

user330315
user330315

Reputation:

LIKE is for strings, not for numbers. Assuming id is actually a number, you first need to cast it to a string in order to be able to apply a LIKE condition on it.

But once you do that, you can use an array for that:

SELECT * 
FROM caller 
WHERE id::text LIKE ANY (array['%805%', '%800', '81%']);

Upvotes: 2

klin
klin

Reputation: 121889

Use any() with an array of searched items:

with test(id, col) as (
values
    (1, 'x800'),
    (2, 'x855'),
    (3, 'x900'),
    (4, 'x920')
    )
select *
from test
where col like any(array['%800', '%855'])

 id | col  
----+------
  1 | x800
  2 | x855
(2 rows)

This is shorter to write but not faster to execute I think.

Upvotes: 1

Related Questions