StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POMassaging varchar data into numbers with mysql
text
Body
copied!<p>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. </p> <p>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). </p> <p>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. </p> <p>I need some way of massaging the data into a more useful form, as such: </p> <ul> <li>1234567.89 -> 1234567.89 (simply casting will work here)</li> <li>1234567.89$ -> 1234567.89 (Casting these seems to give the correct result) </li> <li>£1234567.89 -> 1234567.89 (Casting returns 0) </li> <li>1,234,567,89 -> 1234567.89 (Casting here returns 1)</li> <li>1.234.567.89 -> 1234567.89 (Casting gives 1.234) </li> <li>123 + 456 -> 579.00 (No freaking idea how I'm going to deal with these) </li> <li>NaN or other non-numerical data -> 0 (No sensible way of dealing with these, so just inserting 0 will have to do)</li> </ul> <p>I will also, naturally, have to be able to deal with cases with multiple faults, such as $1,234.567.89. </p> <p>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. </p> <p>If you could help with this I'd really appreciate it. </p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload