I need to test a field value in the WHERE clause of a SELECT statement. With
Microsoft database products, it would look like this:

WHERE IsNumeric(MyField)

If I have a text field that contains an account number such as 9209834, for
example, the function returns true; but if the account number is LAX-0933V, the
function returns false.

I've found a couple of possible ways to do this in MySql, but I'm not sure what
the best approach is:

(a) WHERE CONVERT(MyField, SIGNED INTEGER) IS NOT NULL

(b) WHERE myField REGEXP ('[0-9]')

I'm inclined to go with option (a).

Is there a better way?

deko wrote:
> I need to test a field value in the WHERE clause of a SELECT statement.
> With Microsoft database products, it would look like this:
>
> WHERE IsNumeric(MyField)
>
> If I have a text field that contains an account number such as 9209834,
> for example, the function returns true; but if the account number is
> LAX-0933V, the function returns false.
>
> I've found a couple of possible ways to do this in MySql, but I'm not
> sure what the best approach is:
>
> (a) WHERE CONVERT(MyField, SIGNED INTEGER) IS NOT NULL
>
> (b) WHERE myField REGEXP ('[0-9]')
>
> I'm inclined to go with option (a).

MySQL's CONVERT() function is for translating character sets, not
parsing numeric values from strings.

CAST(MyField AS SIGNED INTEGER) won't work the way you want it to,
either. It doesn't return NULL when it parses alpha or mixed
alpha-numeric strings. It returns the numeric value of any leading
numeric characters in the string, or 0 if no leading numeric characters
are found.

For example:

SELECT CAST('123abc' AS SIGNED INTEGER);
returns numeric value 123.

SELECT CAST('abc132' AS SIGNED INTEGER);
returns numeric value 0.

So you'll have to use a regular expression.

But WHERE myField REGEXP ('[0-9]') won't work either. It matches the
string if the string contains a single digit character, but the string
may contain other non-numeric characters.

Any of the following should work:

WHERE myField REGEXP '^[0-9]+$'
WHERE myField REGEXP '^[[:digit:]]+$'
WHERE myField NOT REGEXP '[^0-9]'

Regards,
Bill K.

> Any of the following should work:
>
> WHERE myField REGEXP '^[0-9]+$'
> WHERE myField REGEXP '^[[:digit:]]+$'
> WHERE myField NOT REGEXP '[^0-9]'

I'm testing now.

WHERE myField REGEXP '^[0-9]+$'

Should do it.

Thanks for the tip!