SQL LIKE condition for validating an integer?

Question

I use a set of SQL LIKE conditions to go through the alphabet and a list of all elements starting with the corresponding letter, for example. to get all books where the name begins with the letter "A":

SELECT * FROM books WHERE title ILIKE "A%" 

This is good for letters, but how do I list all the elements, starting with any number? What is it worth in DB Postgres.

Answer 1

What will choose (by regular expression) every book that has a title starting with a number, is that what you want?

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

If you want integers to start with specific digits, you can use:

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

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

 SELECT * FROM books WHERE price BETWEEN 123000 AND 123999; 

Answer 2

PostgreSQL supports regular expressions matching .

So your example will look like

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

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

Answer 3

Assuming you are looking for "numbers starting with 7" and not "lines starting with 7", maybe something like

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

Or regardless of the equivalent of Postgres conversion.

Answer 4

Which index can I index?

This is definitely btree-indexable:

 WHERE title >= '0' AND title < ':' 

Note that ':' appears after "9" in ASCII.

Answer 5

In PostreSQL, you can use the 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]%$'; 

Answer 6

If you want to search as a string, you can use the text as follows:

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

Answer 7

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

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