GBase 8a将数字转字符串to_char函数功能介绍和样例

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)

参考

GBase 8a将日期转字符串to_char函数功能介绍和样例

GBase 8a从字符串转数字类型的几种方法

Base 8a数据库函数date_format将日期格式化成指定格式的文本