How to Select Numeric Values from a VARCHAR Column

  • by
You may put some values which are potentially numeric in a VARCHAR column, and you would like to find them out for more operations. Unfortunately, MySQL does not provide kinds of function like isNumeric() for developers.

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.

Leave a Reply

Your email address will not be published. Required fields are marked *