SQL LIKE condition to check for integer

Question

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

User · Answer

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  gt  89999 AND price  lt  90100

User · Answer

If you want to search as string  you can cast to text like this   SELECT   FROM books WHERE price  TEXT LIKE  123

User · Answer

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

User · Answer

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

User · Answer

In PostreSQL you can use SIMILAR TO operator  more    -- only digits select   from books where title similar to    0-9      -- start with digit select   from books where title similar to    0-9

User · Answer

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 · Answer

Which one of those is indexable   This one is definitely btree-indexable   WHERE title  gt    0  AND title  lt        Note that     comes after  9  in ASCII

User · Answer

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

[sql] SQL LIKE condition to check for integer?

Examples related to sql

Examples related to postgresql