The function of Oracle to_char is to convert numeric type or date type into character type. The following is a detailed introduction to the use of Oracle to_char function, hoping to be helpful to you.
The Postgres formatting function provides an effective set of tools for converting various data types (date/time, int, float, numeric) to formatted strings and conversely from formatted strings to original data types. Note: The second parameter of all formatting functions is the template used for transformation.
Table 5-7. Formatting functions
| 函数 | 返回 | 描述 | 例子 |
|---|---|---|---|
| to_char(timestamp, text) | text | 把 timestamp 转换成 string | to_char(timestamp ‘now’,‘HH12:MI:SS’) |
| to_char(int, text) | text | 把 int4/int8 转换成 string | to_char(125, ‘999’) |
| to_char(float, text) | text | 把 float4/float8 转换成 string | to_char(125.8, ‘999D9’) |
| to_char(numeric, text) | text | 把 numeric 转换成 string | to_char(numeric ‘-125.8’, ‘999D99S’) |
| to_date(text, text) | date | 把 string 转换成 date | to_date(‘05 Dec 2000’, ‘DD Mon YYYY’) |
| to_timestamp(text, text) | date | 把 string 转换成 timestamp | to_timestamp(‘05 Dec 2000’, ‘DD Mon YYYY’) |
| to_number(text, text) | numeric | 把 string 转换成 numeric | to_number(‘12,454.8-’, ‘99G999D9S’) |
Table 5-8. Templates for date/time Transformations
| 模板 | 描述 |
|---|---|
| HH | 1天的小时数 (01-12) |
| HH12 | 1天的小时数 (01-12) |
| HH24 | 1天的小时数 (00-23) |
| MI | 分钟 (00-59) |
| SS | 秒 (00-59) |
| SSSS | 午夜后的秒 (0-86399) |
| AM or A.M. or PM or P.M. | 正午标识（大写） |
| am or a.m. or pm or p.m. | 正午标识（小写） |
| Y,YYY | 带逗号的年（4 和更多位） |
| YYYY | 年（4和更多位） |
| YYY | 年的后3位 |
| YY | 年的后两位 |
| Y | 年的最后1位 |
| BC or B.C. or AD or A.D. | 年标识（大写） |
| bc or b.c. or ad or a.d. | 年标识（小写） |
| MONTH | 全长大写月份名（9字符） |
| Month | 全长混合大小写月份名（9字符） |
| month | 全长小写月份名（9字符） |
| MON | 大写缩写月份名（3字符） |
| Mon | 缩写混合大小写月份名（3字符） |
| mon | 小写缩写月份名（3字符） |
| MM | 月份 (01-12) |
| DAY | 全长大写日期名（9字符） |
| Day | 全长混合大小写日期名（9字符） |
| day | 全长小写日期名（9字符） |
| DY | 缩写大写日期名（3字符） |
| Dy | 缩写混合大小写日期名（3字符） |
| dy | 缩写小写日期名（3字符） |
| DDD | 1年里的日子(001-366) |
| DD | 1个月里的日子(01-31) |
| D | 1周里的日子(1-7；SUN=1) |
| W | 1个月里的周数 |
| WW | 1年里的周数 |
| CC | 世纪（2 位） |
| J | Julian 日期（自公元前4712年1月1日来的日期） |
| Q | 季度 |
| RM | 罗马数字的月份（I-XII；I=JAN）－大写 |
| rm | 罗马数字的月份（I-XII；I=JAN）－小写 |
All templates allow prefix and suffix modifiers. Modifiers are always allowed in templates. The prefix ‘FX’ is just a global modifier.
Table 5-9. Suffixes for date/time template to_char ()
| 后缀 | 描述 | 例子 |
|---|---|---|
| FM | 填充模式前缀 | FMMonth |
| TH | 大写顺序数后缀 | DDTH |
| th | 小写顺序数后缀 | DDTH |
| FX | 固定模式全局选项（见下面） | FX Month DD Day |
| SP | 拼写模式（还未实现） | DDSP |
Instructions for usage:
If the FX option is not used, to_timestamp and to_date ignore white space. FX must be declared as the first entry in the template. The backslash (”\”) must be used as a double backslash (”\”), such as’\\ HH\\ MI\\ SS ’. Strings between double quotation marks (’”’) are ignored and not parsed. If you want to write double quotation marks to the output, you must place a double backslash (’\\’) before the double quotation marks, such as’\\ “YYYY Month\\” ’. to_char supports text without leading double quotation marks (’”’), but any strings between double quotation marks are processed quickly and are also guaranteed not to be interpreted as template keywords (for example: ’” Hello Year: “YYYY’).
Table 5-10. Templates for to_char (numeric)
| 模板 | 描述 |
|---|---|
| 9 | 带有指定位数的值 |
| 0 | 前导零的值 |
| . （句点） | 小数点 |
| , （逗号） | 分组（千）分隔符 |
| PR | 尖括号内负值 |
| S | 带负号的负值（使用本地化） |
| L | 货币符号（使用本地化） |
| D | 小数点（使用本地化） |
| G | 分组分隔符（使用本地化） |
| MI | 在指明的位置的负号（如果数字 < 0） |
| PL | 在指明的位置的正号（如果数字 > 0） |
| SG | 在指明的位置的正/负号 |
| RN | 罗马数字（输入在 1 和 3999 之间） |
| TH or th | 转换成序数 |
| V | 移动 n 位（小数）（参阅注解） |
| EEEE | 科学记数。现在不支持。 |
Instructions for usage:
Signed words using ‘SG’, ‘PL’, or ‘MI’ are not attached to numbers; For example, to_char (-12, ‘S9999’) generates’-12 ‘and to_char (-12,’ MI9999 ’) generates’-12 ’. The implementation in Oracle does not allow MI to precede 9, but requires 9 to precede MI. PL, SG, and TH are Postgres extensions. 9 indicates the number of digits that are the same as the number of digits in the 9 string. If no numbers are available, use 1 white space. TH does not convert values less than zero, nor does it convert decimals. TH is an Postgres extension. V conveniently multiplies the input by 10 ^ n, where n is the number following V. to_char does not support the use of V tied to 1 decimal point (for example. “99.9 V99” is not allowed).
Table 5-11. Examples of to_char
| 输入 | 输出 |
|---|---|
| to_char(now(),‘Day, HH12:MI:SS’) | ‘Tuesday , 05:39:18’ |
| to_char(now(),‘FMDay, HH12:MI:SS’) | ‘Tuesday, 05:39:18’ |
| to_char(-0.1,‘99.99’) | ’ -.10’ |
| to_char(-0.1,‘FM9.99’) | ‘-.1’ |
| to_char(0.1,‘0.9’) | ’ 0.1’ |
| to_char(12,‘9990999.9’) | ’ 0012.0’ |
| to_char(12,‘FM9990999.9’) | ‘0012’ |
| to_char(485,‘999’) | ’ 485’ |
| to_char(-485,‘999’) | ‘-485’ |
| to_char(485,‘9 9 9’) | ’ 4 8 5’ |
| to_char(1485,‘9,999’) | ’ 1,485’ |
| to_char(1485,‘9G999’) | ’ 1 485’ |
| to_char(148.5,‘999.999’) | ’ 148.500’ |
| to_char(148.5,‘999D999’) | ’ 148,500’ |
| to_char(3148.5,‘9G999D999’) | ’ 3 148,500’ |
| to_char(-485,‘999S’) | ‘485-‘ |
| to_char(-485,‘999MI’) | ‘485-‘ |
| to_char(485,‘999MI’) | ‘485’ |
| to_char(485,‘PL999’) | ‘+485’ |
| to_char(485,‘SG999’) | ‘+485’ |
| to_char(-485,‘SG999’) | ‘-485’ |
| to_char(-485,‘9SG99’) | ‘4-85’ |
| to_char(-485,‘999PR’) | ‘<485>‘ |
| to_char(485,‘L999’) | ‘DM 485 |
| to_char(485,‘RN’) | ’ CDLXXXV’ |
| to_char(485,‘FMRN’) | ‘CDLXXXV’ |
| to_char(5.2,‘FMRN’) | V |
| to_char(482,‘999th’) | ’ 482nd’ |
| to_char(485, ‘“Good number:“999’) | ‘Good number: 485’ |
| to_char(485.8,‘“Pre-decimal:“999” Post-decimal:” .999’) | ‘Pre-decimal: 485 Post-decimal: .800’ |
| to_char(12,‘99V999’) | ’ 12000’ |
| to_char(12.4,‘99V999’) | ’ 12400’ |
| to_char(12.45, ‘99V9’) | ’ 125’ |
The simplest application of the Oracle to_char function:
/*1.0123--- > ‘1.0123’*/ Select TO_CHAR(1.0123) FROM DUAL /*123--- > ‘123’*/ Select TO_CHAR(123) FROM DUAL
Next, look at the following:
/*0.123 --- > ‘.123’ */ SELEC TO_CHAR(0.123) FROM DUAL
The above result ’. 123’ is not the result we want in most cases. What we want should be ‘0.123’.
Let’s look at the specific usage of the to_char function under 1:
TO_CHAR ( n [, fmt [, ‘nlsparam’]] )
The Oracle to_char function converts n of type NUMBER into a value of type VARCHAR2 in numeric format fmt. ‘nlsparams’ specifies the characters returned by elements in numeric format, including:
Decimal point character Group separator Local coin symbols International Coin Symbols
The variable is of the form:
‘NLS_NUMERIC_CHARACTERS=“dg” NLS_CURRENCY=“tcxt” NLS_ISO_CURRENCY=territory’
Where d is the decimal point character and g is the group separator.
Example: TO_CHAR (17145, ‘L099G999’, ‘NLS_NUMERIC_CHARACTERS= ”.,” NLS_CURRENCY= “NUD”’) = NUD017, 145 From the above understanding, looking at some formats of fmt, we can get the value of ‘0.123’ with the following expression:
/*0.123 --- > ’ 0.123’ */ Select TO_CHAR(0.123,‘0.999’) FROM DUAL /*100.12 --- > ’######’ */ Select TO_CHAR(100.12,‘0.999’) FROM DUAL /*1.12 --- > ’ 1.120’ */ Select TO_CHAR(1.12,‘0.999’) FROM DUAL ‘0.123’ comes out, but there is another space in front of it.
The value for 100.12 is # # # # # #, and the value for ‘1.12’ becomes’ 1.120 ’.
We re-identified a new requirement:
1. Remove the blanks 2. The maximum number of decimal places is 4, and the minimum number of decimal places is 2.
1--- > ‘1.00’; 1.1— > ‘1.00’; 1.12— > ‘1.12’; 1.1234— > ‘1.1234’;
1.12345--- > ‘1.1235’
The final implementation is as follows:
/* FM: Except spaces 999999.0099: The maximum positive number on the left side of the decimal point is allowed to be 7 digits, and the minimum 2 digits and the maximum 4 digits on the right side of the decimal point are allowed, and 4 rounding and 5 entering are carried out in the fifth digit */ Select TO_CHAR(123.0233,‘FM9999999.0099’) FROM DUAL