CODEWITHSUNDEEP
phpmysql
Uuid
Uuid

Reputation: 2546

How can I select rows only where first digit is a number from 0 to 9?

As far as I know I can do something like:

"SELECT * 
 FROM my_table 
 WHERE my_field LIKE '0%' 
 OR my_field LIKE '1%' 
 OR my_field LIKE '2%' ";

Is there a way to achieve this with a regular expression or something like this:

"SELECT * 
 FROM my_table 
 WHERE my_field LIKE [only first char is 0-9]"??

EDIT: The field is not numeric and it can be something like "1 People", "211 Pies" and so on.

Upvotes: 5

Views: 449

Answers (5)

user1655741
user1655741

Reputation:

A simple one without regular expressions:

SELECT *, SUBSTR(my_field, 1, 1) AS mf FROM my_table HAVING mf BETWEEN '0' AND '9';

The quotes in the between clause are important!

Upvotes: 0

Jack M.
Jack M.

Reputation: 1170

Try this

SELECT * 
FROM BadlyDesignedTable 
WHERE AnswerColumn RLIKE '^[0-9]+'

I was wondering if it was even possible to regex in where, found it on google in 30 seconds.

Upvotes: 7

user2175485
user2175485

Reputation: 21

Select * From my_table Where (REGEXP_LIKE(my_field, '[[:digit:]]%'))

The (REGEXP_LIKE(Source_String, '[[:character class:]]')) is a function you can use for numerous issues such as the one you have. Simply use it to tell it to do that for the first digit.

http://docs.oracle.com/cd/B14117_01/server.101/b10759/conditions018.htm

EDIT: Select * From my_table Where (SUBSTR(my_field,1,1) = '[[:digit:]]') Tried this in a similar query of mine, and it works.

Upvotes: 1

Matt Busche
Matt Busche

Reputation: 14333

SELECT *
FROM my_table
WHERE left(my_field,1) REGEXP '^[0-9]+';

If you have MSSQL you can use

SELECT *
FROM my_table
WHERE isNumeric(left(my_field,1)) = 1

Upvotes: 0

mavili
mavili

Reputation: 3424

SELECT * FROM table WHERE field REGEXP '^[0-9]'

Upvotes: 2

Related Questions