开发者

Selecting financial values from db stored as text

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 ther开发者_StackOverflow中文版e 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)


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
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜