One solution mentioned here is to use regular expression to match values which are potentially numeric by the comparison operator REGEXP (or RLIKE). In this post, I introduce some regular expression patterns for different kinds of situation.

For matching integer numbers only, you can use the pattern:

^[0-9]+$

`mysql> select '79' regexp '^[0-9]+$' as result;`

+--------+

| result |

+--------+

| 1 |

+--------+

1 row in set (0.00 sec)

The result "1" means true, selected or found.

The above pattern is same as:

^[[:digit:]]+$

`mysql> select '79' regexp '^[[:digit:]]+$' as result;`

+--------+

| result |

+--------+

| 1 |

+--------+

1 row in set (0.00 sec)

For matching not only integers, but decimals also, the fastest way is to add a dot (.) to the above pattern:

^[0-9.]+$

`mysql> select '79.99' regexp '^[0-9.]+$' as result;`

+--------+

| result |

+--------+

| 1 |

+--------+

1 row in set (0.00 sec)

This pattern will match the decimals without leading zero, too. Try to match .99

`mysql> select '.99' regexp '^[0-9.]+$' as result;`

+--------+

| result |

+--------+

| 1 |

+--------+

1 row in set (0.00 sec)

If you don't want to match the leading-zero-omitted decimals, you should use a safer pattern like this:

^[0-9]+.?[0-9]+$to match both integers and decimals. Yes, it looks more complicate, but more flexible.

`mysql> select '79' regexp '^[0-9]+.?[0-9]*$' as result;`

+--------+

| result |

+--------+

| 1 |

+--------+

1 row in set (0.00 sec)

mysql> select '79.99' regexp '^[0-9]+.?[0-9]*$' as result;

+--------+

| result |

+--------+

| 1 |

+--------+

1 row in set (0.00 sec)

If the number has a leading currency sign like a dollar ($), you should use the pattern:

^\$?[0-9]+.?[0-9]+$

`mysql> select '$79.99' regexp '^\$?[0-9]+.?[0-9]+$' as result;`

+--------+

| result |

+--------+

| 1 |

+--------+

1 row in set (0.00 sec)

mysql> select '79.99' regexp '^\$?[0-9]+.?[0-9]+$' as result;

+--------+

| result |

+--------+

| 1 |

+--------+

1 row in set (0.00 sec)

Please beware of the dollar sign which originally indicates the end of a string in regular expression. If you'd like to use it as a plain character without any special meanings, you should escape it by a double backslash (\). Otherwise, you will get ERROR 1139.

For further information about ERROR 1139, please refer to this post:

*How to Resolve ERROR 1139 (42000): Got error 'repetition-operator operand invalid' from regexp*

If the number contains a plus or a minus sign, you should use the pattern:

^(-|\+)?\$?[0-9]+.?[0-9]+$

`mysql> select '+79.99' regexp '^(-|\+)?\$?[0-9]+.?[0-9]+$' as result;`

+--------+

| result |

+--------+

| 1 |

+--------+

1 row in set (0.00 sec)

mysql> select '-79.99' regexp '^(-|\+)?\$?[0-9]+.?[0-9]+$' as result;

+--------+

| result |

+--------+

| 1 |

+--------+

1 row in set (0.00 sec)

mysql> select '+$79.99' regexp '^(-|\+)?\$?[0-9]+.?[0-9]+$' as result;

+--------+

| result |

+--------+

| 1 |

+--------+

1 row in set (0.00 sec)

mysql> select '-$79.99' regexp '^(-|\+)?\$?[0-9]+.?[0-9]+$' as result;

+--------+

| result |

+--------+

| 1 |

+--------+

1 row in set (0.00 sec)

Same reason as escaping dollar signs, you should add a double backslash to escape the plus sign.