Sybase T sql store procedure to substitute the negative number to negative parenthesis with case
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,
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)
from ##tempARFiveColTb where Fund_WKS_Item_Date like ‘%’ + @date +’%’
19316 Dec 31 2011 12:00AM (3.00)
19316 Dec 31 2011 12:00AM 5.00
19316 Dec 31 2011 12:00AM —
the last one is null so in my code it convert to “–”
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 !!