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

函数 返回 描述 例子
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'*/

Next, look at the following:

/*0.123 --- > '.123' */

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:


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

Related articles: