sqlite matching on a number

user_243467 0
Viewed 126

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.

sqlite sql
2 Answers

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'
user_104988280

also since found this too work too:

SELECT COUNT(*)
FROM numbers
WHERE numbers GLOB '[0-9]*'
user_2434670
Related