How to use Oracle to_char function
- 2021-12-19 07:15:14
- OfStack
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
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)
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