SQL to_char 货币格式
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25545224/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL to_char Currency Formatting
提问by Law
I am facing a to_char()currency formatting problem here.
我在to_char()这里面临货币格式问题。
The below code is working for me:
以下代码对我有用：
SELECT TO_CHAR(10000,'L99G999D99MI',
'NLS_NUMERIC_CHARACTERS = ''.,''
NLS_CURRENCY = ''$''') "Amount"
FROM DUAL;
which will provide me with the output: $10,000.00.
Now, I want to convert the currency into a France currency, which the desire output is 10?000,00or a Switzerland currency with output 10'000.00. So, I modified the code as shown below for both of the case above:
这将为我提供输出：$10,000.00.
现在，我想将货币转换为法国货币，即希望输出的10?000,00货币或瑞士货币的输出10'000.00。因此，我针对上述两种情况修改了如下所示的代码：
SELECT TO_CHAR(10000,'L99G999D99MI',
'NLS_NUMERIC_CHARACTERS = ''"", ""''
NLS_CURRENCY = ''$'' ') "Amount"
FROM DUAL;
SELECT TO_CHAR(10000,'L99G999D99MI',
'NLS_NUMERIC_CHARACTERS = ''". "''
NLS_CURRENCY = ''$'' ') "Amount"
FROM DUAL;
But this code does not work and showing an error of ORA-12702. Is there any problem with the code?
但是此代码不起作用并显示ORA-12702. 代码有问题吗？
回答by Captain
If you want to do it in the query:
如果您想在查询中执行此操作：
SELECT TO_CHAR(10000,'L99G999D99MI',
'NLS_NUMERIC_CHARACTERS = ''.''''''
NLS_CURRENCY = ''$'' ') "Amount"
FROM DUAL;
Gives $10'000.00(as this string is getting pre-processed there are pairs of quotes around the characters (becoming single) and then to get a single-quote in the string you need four quotes to become one!)
给出$10'000.00（当这个字符串被预处理时，字符周围有一对引号（变成单引号），然后要在字符串中得到一个单引号，你需要四个引号才能变成一个！）
SELECT TO_CHAR(10000,'L99G999D99MI',
'NLS_NUMERIC_CHARACTERS = '', ''
NLS_CURRENCY = ''$'' ') "Amount"
FROM DUAL;
Gives $10 000,00
给 $10 000,00
回答by Solami
This can be used as well since the decimal notation is already know for French countries
这也可以使用，因为法国国家已经知道十进制表示法
SELECT TO_CHAR(1000000000,'999,999,999,999.99') "Amount" FROM DUAL;
回答by Nick Krasnov
As one of the options, you can set NLS_TERRITORYparameter at a session level:
作为选项之一，您可以NLS_TERRITORY在会话级别设置参数：
alter session set nls_territory='FRANCE';
select to_char(10000, 'fm99G999D00') as french
from dual;
Result:
结果：
FRENCH
----------
10 000,00
alter session set nls_territory='SWITZERLAND';
select to_char(10000, 'fm99G999D00') as switzerland
from dual
Result:
结果：
SWITZERLAND
-----------
10'000.00
