Trailing Negatives...

[js_280]

I have a program that exports columns into a MySQL varchar column.  The vendor exports numbers and strings into this column.  I'm only pulling the fields that have numbers in them and casting them as decimals.  The problem I'm running into is that when the vendor exports the negative numbers, the negative sign comes after the number instead of before and is causing the number to add instead of subtract when SUM()'ng.  Any ideas?

[effigy]

I have a program that exports columns into a MySQL varchar column.

 

Don't you mean imports? You could pass the data through a filter to place the negatives in front. Can you expand on the process? It may be as simple as requiring your vendor to revise their format.

[js_280]

The vendor exports their database via ODBC into MySQL...

 

Anyway...here's what I came up with...

 

SELECT 
table.Qty,
IF(table.Qty REGEXP '-$', (CAST(table.Qty AS DECIMAL) * -1), CAST(table.Qty AS DECIMAL)) AS real_number
from table;

 

Probably overly complicated, but it worked...