SQL LIKE condition to check for integer?

Tested on PostgreSQL 9.5 :

-- only digits

select * from books where title ~ '^[0-9]*$';

or,

select * from books where title SIMILAR TO '[0-9]*';

-- start with digit

select * from books where title ~ '^[0-9]+';

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;

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

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

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

Tags:

Sql

Postgresql