sql - sqlite matching on a number

Question

I can't seem to find a good way to match on an integer in SQLite without writing them all out, for example I'm looking to do something like this:

SELECT count(*)
FROM numbers
WHERE SUBSTR(numbers 1, 1) LIKE ':d'

But the above doesnt work and various versions of it either.

I can get it to work with:

SELECT count(*)
FROM numbers
WHERE SUBSTR(numbers 1, 1) IN ("1","2","3" [etc])

but this seems verbose.

Answer 1

If you want to count the number of rows where the column number starts with a digit:

SELECT COUNT(*)
FROM numbers
WHERE SUBSTR(numbers, 1, 1) BETWEEN '0' AND '9'