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

Question

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.

It worked, I did it like this: WHERE (title LIKE 'S%' OR title LIKE 'T%') AND (replacement_cost BETWEEN 15.99 AND 20.99) Thank you so much for your help. — Nath123, Aug 01 '23 at 16:36
@Nath123: The second pair of parentheses you added is not needed. — Erwin Brandstetter, Aug 01 '23 at 17:22

Erwin Brandstetter · Accepted Answer · 2023-08-02T12:43:23.417

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:

Oh okay, I'm sorry for the late reply I'll accept it it does answer my questions. — Nath123, Aug 04 '23 at 17:06

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

1 Answers1