is_numeric function in mysql?

Databases
1

Hey, I’m looking for a way to find out if a value is numeric in mysql.

ie. SELECT * FROM Table WHERE IS_NUMERIC(field_name)

Can’t seem to find it in the manual.

Thanks in advance.

2

You should already know whether the data contained in the field is numeric or not based on the DATA TYPE OF THE FIELD. If not, you probably did it wrong. Now, if it’s just some number like a ZIP code that you need to validate as numeric, that’s something you should be doing in your application logic and not necessarily via SQL.

3

Well, perhaps some context will help.

I’m actually just trying to select records where the first character in a field is a number - part of an alpha numeric sorting function. (eg. Show all records starting with “j”, or all records starting with a number, etc.) I could do this by getting all records then eliminating the records that don’t fit the criteria in PHP but that’s really wasteful given the high number of overall records.

So the query should actually look more like this:

SELECT * FROM Table WHERE IS_NUMERIC(SUBSTRING(fieldName, 1, 1))

4 
select * from table where fieldname regexp '^[0-9]';

this will not use an index, tho.

5

Try this, and don’t expect to be able to use it on any database besides mysql :stuck_out_tongue:


SELECT *
FROM table
WHERE SUBSTRING(fieldname, 1, 1) REGEXP '[[:digit:]]'

This should find any rows where the first character of fieldname is a number.

6

Beauty! Thanks guys!

7