Search code examples
sqlpostgresqlpattern-matchingsql-like

How can I combine a BETWEEN condition with multiple LIKE expressions?

I'm trying provide film titles that start with the letters "S" and "T", and have a replacement cost of 15.99 to 20.99.

I try to use this code but it still returns some data lower than 15.99 pls help.

SELECT title,length,replacement_cost
FROM film
WHERE title LIKE 'S%' OR title LIKE 'T%'
AND replacement_cost BETWEEN 15.99 AND 20.99;

Still returns data lower than 15.99. See results.

Tried searching for other ways to use LIKE with IN not OR. Maybe that's the problem but I saw a post saying that IN is not usable with LIKE.

Solution

  • Your immediate problem is operator precedence. AND binds before OR - as has been commented. Can be fixed with parentheses:

    SELECT title, length, replacement_cost
FROM   film
WHERE (title LIKE 'S%' OR title LIKE 'T%')
AND    replacement_cost BETWEEN 15.99 AND 20.99

    But you can simplify. Then you don't need OR and, consequently, no parentheses either:

    WHERE  title LIKE ANY ('{S%, T%}')

    And while you only match prefixes the operator ^@ is better, yet. Requires Postgres 11; much improved in Postgres 15:

    WHERE  title ^@ ANY ('{S,T}')

    See: