100

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.

Wayne Koorts
  • 10,861
  • 13
  • 46
  • 72

10 Answers10

192

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;
Johannes Weiss
  • 52,533
  • 16
  • 102
  • 136
  • 1
    When trying op2 I get "Explicit conversion from data type int to text is not allowed." – Gilad Dec 13 '11 at 13:38
  • 2
    If option 2 returns the explicit conversion error mentioned in the comment above (which may happen in SQL Server and perhaps other programs), try using VARCHAR with a large enough max size: SELECT * FROM books WHERE CAST(price AS VARCHAR(20)) LIKE '%123%' – bstrong Jan 20 '17 at 21:33
  • Thank you very much! I can add 5 penny: you can also use: [...] CAST(price AS TEXT) ~* '123%' using operator ~*. Also you can use any regexps inside then, like: [...] CAST(price AS TEXT) ~* '^123\-?456$' – Arsenii May 21 '19 at 14:27
24

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

Vinko Vrsalovic
  • 330,807
  • 53
  • 334
  • 373
17

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

SELECT * FROM books WHERE price::TEXT LIKE '123%'
gellezzz
  • 1,165
  • 2
  • 12
  • 21
5

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]+';
Charlie 木匠
  • 2,234
  • 19
  • 19
2

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.

Corey Porter
  • 1,309
  • 8
  • 9
1

Which one of those is indexable?

This one is definitely btree-indexable:

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

Note that ':' comes after '9' in ASCII.

Brad Koch
  • 19,267
  • 19
  • 110
  • 137
bobflux
  • 11,123
  • 3
  • 27
  • 27
1

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;
skensell
  • 1,421
  • 12
  • 21
0

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]%$';
Máťa - Stitod.cz
  • 885
  • 7
  • 12
0

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)

Ben
  • 767
  • 8
  • 14
-1

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}%`
    )
Jayantha
  • 2,189
  • 1
  • 12
  • 13