CODEWITHSUNDEEP
oracle-databaseformattingnumbersasciioracle-apex
fdl
fdl

Reputation: 107

Oracle: Pattern for to_char(number) to add additional ascii characters?

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.

Upvotes: 5

Views: 31097

Answers (4)

Paul
Paul

Reputation: 1

SYS @ orant11g >select to_char(89.2244, '999G999G999G999G990D00')||'%' from dual;

TO_CHAR(89.2244,'999G999
------------------------
              89.22%

Just use the || bars instead of the concat function.

Upvotes: 0

Juris
Juris

Reputation: 1537

Quick and dirty way:

select to_char(89.2244, '999G999G999G999G990D00L', 'NLS_CURRENCY=''%''') from dual;

Upvotes: 2

Quassnoi
Quassnoi

Reputation: 425341

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%

Upvotes: 3

Barry
Barry

Reputation: 492

So you can't wrap the to_char with a CONCAT?

select concat(to_char(89.2244, '999G999G999G999G990D00'),'%') from dual

Upvotes: 5

Related Questions