Isabel Cariod Isabel Cariod - 3 years ago 55
SQL Question

SQL Select all rows starting with numbers from 0-9

I use SQlite to select rows starting with a certain letter, eg:a, b, c.

SELECT title FROM dictionary WHERE title LIKE 'a%' ORDER BY title ASC
SELECT title FROM dictionary WHERE title LIKE 'b%' ORDER BY title ASC
SELECT title FROM dictionary WHERE title LIKE 'c%' ORDER BY title ASC
...


But I also want to select all titles starting with a number from 0-9, something like that LIKE '0-9%'.

Answer Source

First of all you can tidy up your original LIKE statements by adding OR:

SELECT title FROM dictionary WHERE title LIKE 'a%' OR name like '%b' OR name like '%c' ORDER BY title ASC.

To answer your problem you can use a regex as the title is string based.

WHERE title regexp '^[0-9]+' 

OR

WHERE (LEFT(title, 1) IN ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9'))

You can also try substring and numeric function:

WHERE ISNUMERIC(SUBSTRING(VAL, 1, 1)) 

NOT TESTED but will help you think on how to get this resolved

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download