Massaging varchar data into numbers with mysql

I'm working on updating a system that stores financial information and am using a table structure that uses DECIMAL fields for the data in question.

Unfortunately, my predecessor, in his/her Infinite Wisdom implemented the fields in the old database as varchar. The amount of data input validation that was done also seems to have been light, to put it kindly, and there's all kind of junk data in there. Some fields store the value NaN, some store values formatted as 1,234,567.89, some store values formatted as 1.234.567.89, some store 1234567.89, some include currency symbols at the end, some include currency symbols in the middle, some even contain sums! (123 + 456 for example).

Obviously, casting as DECIMAL can only help with some of these. In cases where the first character isn't numeric I'm going to get 0 back. Worse, in cases where there are commas or more than one decimal point in a number I'm going to get an incorrect result back.

I need some way of massaging the data into a more useful form, as such:

• 1234567.89 -> 1234567.89 (simply casting will work her开发者_Python百科e)
• 1234567.89$ -> 1234567.89 (Casting these seems to give the correct result)
• £1234567.89 -> 1234567.89 (Casting returns 0)
• 1,234,567,89 -> 1234567.89 (Casting here returns 1)
• 1.234.567.89 -> 1234567.89 (Casting gives 1.234)
• 123 + 456 -> 579.00 (No freaking idea how I'm going to deal with these)
• NaN or other non-numerical data -> 0 (No sensible way of dealing with these, so just inserting 0 will have to do)

I will also, naturally, have to be able to deal with cases with multiple faults, such as $1,234.567.89.

I'm thinking Regex is the only option here, but as far as I can tell, MySQL only provides regex matching, it doesn't seem to have any regex replacement features.

If you could help with this I'd really appreciate it.

If you don't want to go out of MySQL, you could always use a combination of control flow functions and regular expressions or replace.

SELECT 
CASE your_field 
WHEN REGEXP '^[0-9\.]*\$$' THEN DECIMAL(REPLACE(your_field,'$',''))
WHEN REGEXP...

If you need features not present in the mysql regex default implementation, you could always use an UDF like this one that offers your more advanced features like group capturing or replace.

BTW, have you considered going "outside" of MySQL and use a programming language you're comfortable with to connect to your MySQL and update the new fields programatically?

I suppose you've maybe thought of this, but could it be easier to instead pull the data into (say) a CSV file, and then write a script to do the data-massaging, and then put it back into the database (matching up csv rows to database table rows using a key from the table)?

You can take care of most of these with the REPLACE function (e.g. set mycol = REPLACE(mycol,'$','')).

For something like 1.234.567.89, if you know that you have exactly two decimal places, you can use REPLACE(mycol,'.','') and then divide by 100.

For cases like 123 + 456 you could do something fancy with the SUBSTR and POSITION functions -- use POSITION to find the +, and then SUBSTR to get what's before and after it. The SUBSTRING_INDEX function might also be useful here.

I feel there's no sensible way of doing this without resorting to a scripting language, so I've written the following PHP code to address the problem.

function notEmptyString ($val)
{
    return ($val !== '');
}

/**
 * Make an attempt at extracting menaingful numeric data from a string that can contain all kinds of garbage
 * @param string $string
 * @return int
 */
function mungeNumber ($string)
{
    $num    = 0;
    if (($digits = preg_split ('/[^0-9]/', $string))
    && ($digits = array_filter ($digits, 'notEmptyString')))
    {
        $decimal    = (count ($digits) > 1)? array_pop ($digits): 0;
        $num        = (implode ('', $digits) . '.' . $decimal) * 1;
    }
    return ($num);
}

So far it seems to have coped with all the test data I've given it, though I'm still coming up with some more suitably pathological test cases for it to cope with. I know for a fact that it won't deal with the cases where the value seems to be a sum, but i don't think there's a great deal I can do about that, and the times where that seems to be the case are mercifully small.

When a field contains two or more distinct numbers the result will be a single number, which is unfortunate. However, the numbers in question will be excessively large compared to the others in the set so should be easy to spot and deal with manually.