Menu
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.
449
LIKE is a string operator and has nothing to do with integers. You can kludge this sort of stuff by casting your integers as strings.
Syntax to check if the value is an integer. select yourColumnName from yourTableName where yourColumnName REGEXP '^-?[0-9]+$'; The query wherein we have used regular expression. This will output only the integer value.
The SQL LIKE clause is used to compare a value to similar values using wildcard operators. There are two wildcards used in conjunction with the LIKE operator. The percent sign represents zero, one or multiple characters. The underscore represents a single number or character.
The keyword ILIKE can be used instead of LIKE to make the match case insensitive according to the active locale. This is not in the SQL standard but is a PostgreSQL extension. The operator ~~ is equivalent to LIKE , and ~~* corresponds to ILIKE .
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 love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
