Thursday, May 2

mysql note: IsNumeric() functionality

Situation: There is one text field "old" in the table a, and it can be:
?
NON
2+
0
4.0
-0.3
=======
The task is to identify the numbers (the last 3 items) and put the numbers into a double filed "new". This field is currently set as default (null) at this moment. So the new field would be:
(null)
(null)
(null)
0
4.0
-0.3
=========
I can't use "update a set new = old". The result is:
0
0
0
0
4
0.3
========
So the idea is to use a "IsNumeric()" function to "update a set new = old where IsNumeric(old)", so only the last 3 items that are numeric are being updated.

There is such a function in MS Sql Server, but not in mysql. The first Googled page gives one way:
update a set new = old where old = concat( '', 0 + old )
The result is:
(null)
(null)
(null)
0
(null)
-0.3
========
The reason is that when they are converted to string, 4.0 does not equal to 4.

Another Googled page claims to have "MySQL Equivalent of ISNUMERIC()", and the solution is using regex:
update a set new = old where old  REGEXP ('[0-9]');
The result is:

(null)
(null)
2
0
4.0
-0.3
========

Because this regex is looking for any string that contains number, the "2+" is selected, and that is wrong.

A popular regex to verify number is '^[-+]?[0-9]*\.?[0-9]*$', but when it is being used, the result is not right:

update a set new = old where old  REGEXP ('^[-+]?[0-9]*\.?[0-9]*$');
The result is:

0
(null)
2
0
4.0
-0.3
========


I can't understand why '?' and '2+' can pass the regex verification. Maybe the regex implementation of mysql is not quite standard.

Finally, this regex gives out what I need: '^(([0-9+-.$]{1})|([+-]?[$]?[0-9]*(([.]{1}[0-9]*)|([.]?[0-9]+))))$' (From this page)

I am sorry, this regex is too long to understand, and I am exhausted already. Please check that page to understand what's the meaning if interested. For now, I can just use it as:
update a set new = old where old  REGEXP ('^(([0-9+-.$]{1})|([+-]?[$]?[0-9]*(([.]{1}[0-9]*)|([.]?[0-9]+))))$');

(null)
(null)
(null)
0
4.0
-0.3
=========

Problem solved.

BTW, the regex in mysql can only be used for validations like this case, can not be used for string replacement, such as retrieving number from a string. That limits the moves we can have. I hate that.

Labels: ,