CODEWITHSUNDEEP
sqlsqlite
Isabel Cariod
Isabel Cariod

Reputation: 363

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%'.

Upvotes: 0

Views: 3221

Answers (3)

Enoch
Enoch

Reputation: 1001

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

Upvotes: 3

Kemal Güler
Kemal Güler

Reputation: 614

Try to use regexp 

SELECT title FROM dictionary WHERE title regexp '^[0-9]+';

Upvotes: 1

user5934437
user5934437

Reputation:

You can try this

SELECT * FROM YourTable WHERE YourColumn regexp '^[0-9]+'

Upvotes: 1

Related Questions