Only Numeric Results In Mysql

Hey,

Does somebody know how i could get only numeric results from a Mysql table.

Something like this.

SELECT * FROM Table WHERE Something LIKE '1%'

But i only want it to be the number 1 till 9
so is there any wildcard that works for the number 1 to 9.

Regards,
Kaju

 

For 0-9:

SELECT * FROM Table WHERE Something REGEXP '^[0-9]+$'


For 1-9:

SELECT * FROM Table WHERE Something REGEXP '^[1-9]+$'


Starting with 1-9:

SELECT * FROM Table WHERE Something REGEXP '^[1-9][0-9]+$'

 

Hey,

It works.
Thank you very much.

Regards,
Kaju

 

Hey,

I got a problem now, it does work but when the name contains a letter it wont show it.
example: 1minute

what i want is that it looks if the first letter is a number or not if so then it shows it with the hole name.
can somebody help me with this.

Regards,
Kaju

 

try
SELECT * FROM Table WHERE Something REGEXP '^[1-9][0-9][a-zA-Z]+$'

 

Hmm... no offence, but maybe you should reconsider exactly what it is you're storing in the database?

If you're storing numbers, you're storing numbers... and you shouldn't be storing them as a string. There's probably a much better way of doing what you're trying to do is what I'm trying to say. Please, for the love of all things good, don't go down the path of using a database as essentially somewhere to randomly dump strings!

 

hey,

i already found a solution to my problem, i just used [A-Za-z0-9].

thanks for the help

Regards,
Kaju

 

One possible solution (which would dramatically improve select performance) is to have a field that only contains the numeric portion of the field you're selecting against. The column could simply be NULL if it doesn't contain any numeric value, and if it does, it would only include the numeric part of the string.

You'd need to parse out the number using a regular expression as you are inserting or updating data in the row, but your select query can get a lot simpler:

SELECT * FROM TABLE WHERE NUMVAL IS NOT NULL

Code (markup):