Selecting financial values from db stored as text

Question

I have some financial values stored as text in a mysql db. the significance of financial is that negative numbers are stored enclosed in paranthesis. is there a way to automatically get the numeric value associated with that text. (like '5' shoudl be retuned as 5 and '(5)' should be returned as -5)

Answer 1

You probably know that enclosing negative values in parentheses is a presentational issue and should not even be in the database to begin with. There are numeric data types for financial values that perfectly cover the negative range and are easy to select/manipulate/aggregate.

Now you are stuck with something horrible along the lines of this:

SELECT
  CASE WHEN LEFT(val, 1) = '('
       THEN -1 * CAST( REPLACE((val, '(', ''), ')', '') AS DECIMAL(10,4))
       ELSE CAST( val AS DECIMAL(10,4) )
  END AS num_val
FROM
  val_table