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.

DateDiff function in SQL Server

This DateDiff function in SQL Server might not be working as you expected.

For example, now we are looking for records that are older than 4 hours, you would think to do it like that:

select * from table where DateDiff(hour, CreateDate, getdate()) >=4

but that is not correct. Assume the CreateDate is 05:59 and the current time is
09:00 , you will find that the this record is selected, as if this 3 hours and 1 minute record is "older than 4 hours."

We can easily confirm that using these 2 queries:

 select datediff(hour, '2013-03-19 05:59', '2013-03-19 09:00')

The time difference is 3 hours and 1 minute, so I would expect this query to return “3”

select datediff(hour, '2013-03-19 05:00', '2013-03-19 09:59')

The time difference is 4 hours and 59 minute, so I would expect this query to return “4”

Actually, these two queries both return “4”.

So this DateDiff function is simply using the "hour" part of the 2 datetime to do the calculation. It use the "9" of the second datetime to substract the "5" of the first datetime to achieve "4".

Back to our initial question: How to look for records that are older than 4 hours? Use DateAdd:

Select * from table where DateAdd(hour, 4, CreateDate) >= getDate()

This will give a precise calculation of 4 hours.

Add on Sept 16, 2013:
The last query can do the work, but it wasted index, if any. The DB has to visit every item to calculate the DateAdd(hour, 4, CreateDate) in order to find out the candidates. 
It's important not to calculate each column, if you want to DB to use existing index of this column.
So the query can be changed to:

Select * from table where CreateDate >= DateAdd(hour, -4, getDate())