GBase 8a数据库集群,提供了和Oracle语法一致的to_char功能,用于格式化数字转字符串,本文介绍该函数的语法和使用样例。
目录导航
语法
TO_CHAR(number,[FORMAT])
说明
将参数 number 转换为字符串,并进行格式化输出。如果 number 的位数大于格式化参数 FORMAT 的参数,结果将以“#”显示。
参数格式
模板 | 描述 |
---|---|
9 | 带有指定位数的值 |
0 | 前导零的值 |
. (句点) | 小数点 |
, (逗号) | 分组(千)分隔符 |
PR | 尖括号内负值 |
S | 带+-符号的值(使用本地化) |
L、$ | 货币符号(使用本地化) |
D | 小数点(使用本地化) |
MI | 在指明的位置的负号(如果数字 < 0) |
EEEE | 科学记数。现在不支持。 |
B、b | 如果 number 的值为 0,则替换为空格,可以出现在任意位置。 示例:B9.99 |
TME | 科学计数法的简化形式,保留精度 |
X、x | 输出16进制数字,等同于HEX数字的结果 |
FM、fm | 去掉对齐位数用的前后空格 |
样例
指定位数的值:9
整数会严格匹配,如果不足则显示#, 小数会自动补齐尾部的0
gbase> select to_char('123.45','9999.999');
+------------------------------+
| to_char('123.45','9999.999') |
+------------------------------+
| 123.450 |
+------------------------------+
1 row in set (Elapsed: 00:00:00.00)
gbase> select to_char('123.45','99.999');
+----------------------------+
| to_char('123.45','99.999') |
+----------------------------+
| ####### |
+----------------------------+
前导0的值:0
在前面补齐0到指定位数。
gbase> select to_char('123.45','00099.999');
+-------------------------------+
| to_char('123.45','00099.999') |
+-------------------------------+
| 00123.450 |
+-------------------------------+
1 row in set (Elapsed: 00:00:00.00)
千分位:,
用于格式化的千分位
gbase> select to_char('123.45','00,099.999');
+--------------------------------+
| to_char('123.45','00,099.999') |
+--------------------------------+
| 00,123.450 |
+--------------------------------+
1 row in set (Elapsed: 00:00:00.00)
gbase> select to_char('123456.789','000,099.999');
+-------------------------------------+
| to_char('123456.789','000,099.999') |
+-------------------------------------+
| 123,456.789 |
+-------------------------------------+
1 row in set (Elapsed: 00:00:00.00)
用尖括号表示负值:PR
就是小于和大于号括起来<>,对正数无效。
gbase> select to_char('-123456.789','000,099.999');
+--------------------------------------+
| to_char('-123456.789','000,099.999') |
+--------------------------------------+
| -123,456.789 |
+--------------------------------------+
1 row in set (Elapsed: 00:00:00.00)
gbase> select to_char('-123456.789','000,099.999PR');
+----------------------------------------+
| to_char('-123456.789','000,099.999PR') |
+----------------------------------------+
| <123,456.789> |
+----------------------------------------+
1 row in set (Elapsed: 00:00:00.00)
显示数据的正负:S
当前版本测试只能出现在尾部,不能在最前面。
gbase> select to_char('123456.789','000,099.999S');
+--------------------------------------+
| to_char('123456.789','000,099.999S') |
+--------------------------------------+
| 123,456.789+ |
+--------------------------------------+
1 row in set (Elapsed: 00:00:00.00)
gbase> select to_char('-123456.789','000,099.999S');
+---------------------------------------+
| to_char('-123456.789','000,099.999S') |
+---------------------------------------+
| 123,456.789- |
+---------------------------------------+
1 row in set (Elapsed: 00:00:00.00)
货币符号:L、$
当前版本测试,L只能出现在最前面,$可以出现在最前和最后。其中L没有出现在标准文档里,不建议使用。
gbase> select to_char('-123456.789','L000,099.999');
+---------------------------------------+
| to_char('-123456.789','L000,099.999') |
+---------------------------------------+
| -$123,456.789 |
+---------------------------------------+
1 row in set (Elapsed: 00:00:00.00)
gbase> select to_char('123456.789','L000,099.999');
+--------------------------------------+
| to_char('123456.789','L000,099.999') |
+--------------------------------------+
| $123,456.789 |
+--------------------------------------+
1 row in set (Elapsed: 00:00:00.00)
-- ---------------¥----
gbase> select to_char('-123456.789','000,099.999$');
+---------------------------------------+
| to_char('-123456.789','000,099.999$') |
+---------------------------------------+
| -$123,456.789 |
+---------------------------------------+
1 row in set (Elapsed: 00:00:00.00)
gbase>
gbase> select to_char('123456.789','000,099.999$');
+--------------------------------------+
| to_char('123456.789','000,099.999$') |
+--------------------------------------+
| $123,456.789 |
+--------------------------------------+
1 row in set (Elapsed: 00:00:00.00)
gbase>
gbase> select to_char('123456.789','$000,099.999');
+--------------------------------------+
| to_char('123456.789','$000,099.999') |
+--------------------------------------+
| $123,456.789 |
+--------------------------------------+
1 row in set (Elapsed: 00:00:00.00)
gbase> select to_char('-123456.789','$000,099.999');
+---------------------------------------+
| to_char('-123456.789','$000,099.999') |
+---------------------------------------+
| -$123,456.789 |
+---------------------------------------+
1 row in set (Elapsed: 00:00:00.00)
显示符号:MI
当前版本只能是末尾,标准文档没有收录,不建议使用。
gbase> select to_char('123456.789','000,099.999MI');
+---------------------------------------+
| to_char('123456.789','000,099.999MI') |
+---------------------------------------+
| 123,456.789 |
+---------------------------------------+
1 row in set (Elapsed: 00:00:00.00)
gbase>
gbase> select to_char('-123456.789','000,099.999MI');
+----------------------------------------+
| to_char('-123456.789','000,099.999MI') |
+----------------------------------------+
| 123,456.789- |
+----------------------------------------+
0值显示空格:B、b
如果参数number是0,则所有的显示转为空格,长度按照格式化要求。
gbase>
gbase> select to_char('0','B000,099.999'),length(to_char('0','B000,099.999'));
+-----------------------------+-------------------------------------+
| to_char('0','B000,099.999') | length(to_char('0','B000,099.999')) |
+-----------------------------+-------------------------------------+
| | 12 |
+-----------------------------+-------------------------------------+
1 row in set (Elapsed: 00:00:00.00)
科学计数法:EEEE、eeee
小数点前面的数字,请不要提供复杂格式,一般就是9即可。小数点后是精度,按需指定。
gbase> select to_char('0.012','9.9eeee');
+----------------------------+
| to_char('0.012','9.9eeee') |
+----------------------------+
| 1.2E-02 |
+----------------------------+
1 row in set (Elapsed: 00:00:00.00)
gbase> select to_char('0123.012','9.900eeee');
+---------------------------------+
| to_char('0123.012','9.900eeee') |
+---------------------------------+
| 1.230E+02 |
+---------------------------------+
1 row in set (Elapsed: 00:00:00.00)
gbase> select to_char('0123.012','9.999eeee');
+---------------------------------+
| to_char('0123.012','9.999eeee') |
+---------------------------------+
| 1.230E+02 |
+---------------------------------+
1 row in set (Elapsed: 00:00:00.00)
gbase> select to_char('0.012','9.999eeee');
+------------------------------+
| to_char('0.012','9.999eeee') |
+------------------------------+
| 1.200E-02 |
+------------------------------+
1 row in set (Elapsed: 00:00:00.00)
科学技术法:TME
不需要指定精度,默认全部保留。如需要指定精度,用EEEE参数
gbase> select to_char('1.012','TME');
+------------------------+
| to_char('1.012','TME') |
+------------------------+
| 1.012E+00 |
+------------------------+
1 row in set (Elapsed: 00:00:00.00)
gbase> select to_char('1.0123456789','TME');
+-------------------------------+
| to_char('1.0123456789','TME') |
+-------------------------------+
| 1.0123456789E+00 |
+-------------------------------+
1 row in set (Elapsed: 00:00:00.00)
16进制输出:X、x
等同于HEX的数字输出结果,区别是前面不足的位置会出现空格补充。如果不需要空格占位,可以用FM去掉空格。
gbase> select to_char('123456789.','XXXXXXXXXXXX');
+--------------------------------------+
| to_char('123456789.','XXXXXXXXXXXX') |
+--------------------------------------+
| 75BCD15 |
+--------------------------------------+
1 row in set (Elapsed: 00:00:00.00)
gbasegbase> select to_char('123456789.','FMXXXXXXXXXXXX');
+----------------------------------------+
| to_char('123456789.','FMXXXXXXXXXXXX') |
+----------------------------------------+
| 75BCD15 |
+----------------------------------------+
1 row in set (Elapsed: 00:00:00.00)
去掉对应用的前后空格:FM
测试时发现尾部的0也被去掉了。
gbase> select to_char('123.456.','9999.9999');
+---------------------------------+
| to_char('123.456.','9999.9999') |
+---------------------------------+
| 123.4560 |
+---------------------------------+
1 row in set (Elapsed: 00:00:00.00)
gbase>
gbase> select to_char('123.456.','FM9999.9999');
+-----------------------------------+
| to_char('123.456.','FM9999.9999') |
+-----------------------------------+
| 123.456 |
+-----------------------------------+
1 row in set (Elapsed: 00:00:00.00)