SQL LIKE condition to check for integer?

Solution 1:

That will select (by a regex) every book which has a title starting with a number, is that what you want?

SELECT * FROM books WHERE title ~ '^[0-9]'

if you want integers which start with specific digits, you could use:

SELECT * FROM books WHERE CAST(price AS TEXT) LIKE '123%'

or use (if all your numbers have the same number of digits (a constraint would be useful then))

SELECT * FROM books WHERE price BETWEEN 123000 AND 123999;

PostgreSQL supports regular expressions matching.

So, your example would look like

SELECT * FROM books WHERE title ~ '^\d+ ?'

This will match a title starting with one or more digits and an optional space

If you want to search as string, you can cast to text like this:

SELECT * FROM books WHERE price::TEXT LIKE '123%'

Assuming that you're looking for "numbers that start with 7" rather than "strings that start with 7," maybe something like

select * from books where convert(char(32), book_id) like '7%'

Or whatever the Postgres equivalent of convert is.

I'm late to the party here, but if you're dealing with integers of a fixed length you can just do integer comparison:

SELECT * FROM books WHERE price > 89999 AND price < 90100;