mercredi 22 avril 2015

Function/Procedure to add values in non-1NF format separated by delimiter

I need a function written in MySQL to add numbers together (positive and negative) that are in a single column and separated by a delimiter. The delimiter in this case is "?". The function also needs to ignore extraneous characters that are not numeric, not a minus sign ("-"), not a period ("."), and not a delimiter (e.g., "?").

An example is a column named "BalAdjAmt" whose values could be:

500.00?500.00?-1000.00
500.00
500.00?500.00
500.00?500.00?-1000.00 B

The result of the function when run on this dataset should be:

0
500.00
1000.00
0

Any pointers would be helpful. Thanks in advance.

Aucun commentaire:

Enregistrer un commentaire