Sybase T sql store procedure to substitute the negative number to negative parenthesis with case
February 28, 2012
I have tried to use a few examples online with stackoverflow.com and etc, but for sybase 12 I can’t find anything to make my store procedure to convert the negative number to negative parenthesis. Worst thing is our database server didn’t enable java and I can’t really use replace_regexp to change the negative number to negative parenthesis. I have used sometime to get around the problem.
Here is the work around here and I only type out part of my code (ignore my temp table and my variable day range:
select distinct ##tempARFiveColTb.Unique_Fund_Code,
##tempARFiveColTb.Fund_WKS_Item_Date, column_1_value = case when ##tempARFiveColTb.column_1_value < 0 then “(“+rtrim(convert(varchar, abs(convert(money, ##tempARFiveColTb.column_1_value))))+”)” when ##tempARFiveColTb.column_1_value = null then “–“ else convert(char, ##tempARFiveColTb.column_1_value) end
from ##tempARFiveColTb where Fund_WKS_Item_Date like ‘%’ + @date +’%’
In my code (look at the bolder part), I have a case inside the select field to make sure if they are negative number, then make sure you make it a absolute number, then convert it into char. In my case, I need to trim the number space but not necessary for others. Then add parenthesis on both side.
If it is not a negative number, then just convert it into char and print it out. It is because I find out that in my code I can’t print out a numeric or a money datatype number but text only. But I think it should work to print out with numeric type with case. But here I just show it out anyway and see it will hard other people to save sometime because many example online has already save my time and I hope I can also help others. Enjoy !!