SQL LIKE condition to check for integer?

user_38403 0
Viewed 193398

I am using a set of SQL LIKE conditions to go through the alphabet and list all items beginning with the appropriate letter, e.g. to get all books where the title starts with the letter "A":

SELECT * FROM books WHERE title ILIKE "A%"

That's fine for letters, but how do I list all items starting with any number? For what it's worth this is on a Postgres DB.

postgresql sql
10 Answers

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]+';
user_58274500

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;
user_22486380

For those who need to find all values that contain a number (regardless of its position) you can just use:

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

(tested with PostgreSQL 13.4)

user_57256190

If you are trying to use like for int column, use CAST to varchar.
(This is Objection RawFunction query example)

.where(
      db.obj.raw("CAST(id AS varchar)"),
      "like",
      `%${data.id}%`
    )
user_77026340
Related