Search code examples
sqlpostgresqlsql-like

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.)


Solution

  • 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%']);