How to format strings to numbers with apostrophe as group separator in Oracle

3.2k views Asked by At

In Switzerland the number format is as following.

1'234.56

with the group separator the apostrophe or simple quote '.

How can I format a string in Oracle so it is shown in this way?

This works for the comma:

select to_char(1234.56, '999G999D99', q'[NLS_NUMERIC_CHARACTERS=.,]') from dual

I tried the same approach with the simple quote:

select to_char(1234.56, '999G999D99', q'[NLS_NUMERIC_CHARACTERS=.']') from dual

But I get this error:

ORA-12702: invalid NLS parameter string used in SQL function
12702. 00000 -  "invalid NLS parameter string used in SQL function"
*Cause:    An unknown parameter name or invalid value is specified in a NLS
           parameter string.
*Action:
2

There are 2 answers

0
Multisync On BEST ANSWER
select to_char(1234.56, '999G999D99', 'NLS_NUMERIC_CHARACTERS=''.''''') from dual;

With a quote operator:

select to_char(1234.56, '999G999D99', q'[NLS_NUMERIC_CHARACTERS='.'']') from dual;
0
Sebri Zouhaier On

Try this

select to_char(1234.56, '999G999D99', 'NLS_NUMERIC_CHARACTERS=''.''''') from dual;