Oracle: Pattern for to_char(number) to add additional ascii characters?
29,596
Solution 1
So you can't wrap the to_char with a CONCAT?
select concat(to_char(89.2244, '999G999G999G999G990D00'),'%') from dual
Solution 2
Quick and dirty way:
select to_char(89.2244, '999G999G999G999G990D00L', 'NLS_CURRENCY=''%''') from dual;
Solution 3
You can't do it right in the number format.
If you are able to change NLS_CURRENCY
for you session, you can do the following:
SELECT TO_CHAR(1.2, '999G999G999G999G990D00L' /*, 'NLS_CURRENCY=%' */)
FROM dual
---
1,20%
Author by
fdl
Updated on July 09, 2022Comments
-
fdl almost 2 years
Using the Oracle to_char(number) function, is it possible to append ascii characters to the returned string?
Specifically, I need to add a percentage character to the returned string.
"select to_char(89.2244, '999G999G999G999G990D00') from dual" --> returns "89.22". I need a format pattern that returns "89.22%".
I am using this through reports in Application Express, so cannot simply concatenate "%" to the query, i need to put it in the number format.
-
fdl about 15 yearsNo, sadly not. In Apex reports, you only specify which number format to use, and the to_char-formatting is done "behind the scenes" in compiled pl/sql, so my only option it to use a number format (or make a css hack)
-
Barry about 15 yearsI couldn't find a way to do this with just the number format string. CSS might be the way to go here.
-
Mr. Shiny and New 安宇 about 15 yearsYou could use a view instead of a table, and do any kind of selection manipulation in the view. Not sure if that is preferrable...
-
fdl about 15 yearsThank you, i guess i only needed to be certain that it is not possible to do it in the number format directly. I will use some kind of html/css hack instead.
-
fdl about 15 yearsUsing a view or selecting the number column as a concatenated string would do, but that rules out automatic summary operations from the tool. Will use CSS, thanks for all the help!