为将不同数据类型(包括日期/时间,整数,浮点数,数值)转换成格式化后的字符和将格式化后 的字符串转换为特定的数据类型,POLARDB for Oracle的格式化函数提供了很多功能 强大的工具。表3-18列出了这些函数。这些函数都是有通用的调用约定:第一个参数是格式化的 值,第二个参数是字符串模板,用于定义输出或输入的格式。

表 1. Formatting Functions
Function Return Type Description Example Result
TO CHAR(DATE [, format ]) VARCHAR2 Convert a date/time to a string with output, format. If omitted default format is DD-MON- YY. TO CHAR(SYSDATE, 'MM/DD/YYYY HH12:MI:SS AM') 07/25/2007 09:43:02 AM
TO CHAR(INTEGER [, format ]) VARCHAR2 Convert an integer to a string with output, format TO CHAR(2412, '999,999S') 2,412+
TO CHAR(NUMBER [, format ]) VARCHAR2 Convert a decimal number to a string with output, format TO CHAR(10125.35, '999,999.99') 10,125.35
TO CHAR(DOUBLE PRECISION, format) VARCHAR2 Convert a floating-point number to a string with output, format TO CHAR(CAST(123.5282 AS REAL), '999.99') 123.53
TO DATE(string [, format ]) DATE Convert a date formatted string to a DATE data type TO DATE('2007-07-04 13:39:10', 'YYYY-MM-DD HH24:MI:SS') 04-JUL-07 13:39:10
TO NUMBER(string [, format ]) NUMBER Convert a number formatted string to a NUMBER data type TO NUMBER('2,412-', '999,999S') -2412
TO TIMESTAMP(string, format) TIMESTAMP Convert a timestamp formatted string to a TIMESTAMP data type TO TIMESTAMP('05 Dec 2000 08:30:25 pm', 'DD Mon YYYY hh12:mi:ss pm') 05-DEC-00 20:30:25

在TO_CHAR函数的输出模板字符串中,这里有固定的模式可以识别,并且由格式化的数值所替代,而任何不是标准模式的文字是简单的逐字拷贝。类似的情况,在一个输入模板字符串中(对除了TO_CHAR以外的其他函数),模板模式能够标识出输入字符串的部分和要寻找的值。

下面的表显示了在函数TO_CHAR和TO_DATE中可以用来格式化数值的模板模式。

表 2. Template Date/Time Format Patterns
Pattern Description
HH Hour of day (01-12)
HH12 Hour of day (01-12)
HH2 4 Hour of day (00-23)
MI Minute (00-59)
SS Second (00-59)
SSSSS Seconds past midnight (0-86399)
AM or A.M. or PM or P.M. Meridian indicator (uppercase)
am or a.m. or pm or p.m. Meridian indicator (lowercase)
Y,YYY Year (4 and more digits) with comma
YEAR Year (spelled out)
SYEAR Year (spelled out) (BC dates prefixed by a minus sign)
YYYY Year (4 and more digits)
SYYYY Year (4 and more digits) (BC dates prefixed by a minus sign)
YYY Last 3 digits of year
YY Last 2 digits of year
Y Last digit of year
IYYY ISO year (4 and more digits)
IYY Last 3 digits of ISO year
IY Last 2 digits of ISO year
I Last 1 digit of ISO year
BC or B.C. or AD or A.D. Era indicator (uppercase)
bc or b.c. or ad or a.d. Era indicator (lowercase)
MONTH Full uppercase month name
Month Full mixed-case month name
month Full lowercase month name
MON Abbreviated uppercase month name (3 chars in English, localized lengths vary)
Mon Abbreviated mixed-case month name (3 chars in English, localized lengths vary)
mon Abbreviated lowercase month name (3 chars in English, localized lengths vary)
MM Month number (01-12)
DAY Full uppercase day name
Day Full mixed-case day name
day Full lowercase day name
DY Abbreviated uppercase day name (3 chars in English, localized lengths vary)
Dy Abbreviated mixed-case day name (3 chars in English, localized lengths vary)
dy Abbreviated lowercase day name (3 chars in English, localized lengths vary)
DDD Day of year (001-366)
DD Day of month (01-31)
D Day of week (1-7; Sunday is 1)
W Week of month (1-5) (The first week starts on the first day of the month)
WW Week number of year (1-53) (The first week starts on the first day of the year)
IW ISO week number of year; the first Thursday of the new year is in week 1
CC Century (2 digits); the 21st century starts on 2001-01-01
SCC Same as CC except BC dates are prefixed by a minus sign
J Julian Day (days since January 1, 4712 BC)
Q Quarter
RM Month in Roman numerals (I-XII; I=January) (uppercase)
rm Month in Roman numerals (i-xii; i=January) (lowercase)
RR First 2 digits of the year when given only the last 2 digits of the year. Result is based upon an algorithm using the current year and the given 2-digit year. The first 2 digits of the given 2- digit year will be the same as the first 2 digits of the current year with the following exceptions:
  • If the given 2-digit year is < 50 and the last 2 digits of the current year is >= 50, then the first 2 digits for the given year is 1 greater than the first 2 digits of the current year.
  • If the given 2-digit year is >= 50 and the last 2 digits of the current year is < 50, then the first 2 digits for the given year is 1 less than the first 2 digits of the current year.
RRRR Only affects TO_DATE function. Allows specification of 2-digit or 4-digit year. If 2-digit year given, then returns first 2 digits of year like RR format. If 4-digit year given, returns the given 4-digit year.
某些修改者可以应用到任何模板模式,以用来修改它的行为。例如,FMMonth是带有FM修改者的Month模式。下面的表显示了针对日期/时间格式的模式修改者。
表 3. Template Pattern Modifiers for Date/Time Formatting
Modifier Description Example
FM prefix Fill mode (suppress padding blanks and zeros) FMMonth
TH suffix Uppercase ordinal number suffix DDTH
th suffix Lowercase ordinal number suffix DDth
FX prefix Fixed format global option (see usage notes) FX Month DD Day
SP suffix Spell mode DDSP
说明
  • FM压缩前面的0和尾部的空格,这些0和空格用于使输出符合固定宽度的模式。
  • 如果没有使用FX选项,TO_TIMESTAMP和TO_DATE跳过输入字符串中的多个空格。必须将Fx指定为模板中第一个成员。例如TO_TIMESTAMP('2000 JUN', 'YYYY MON')这种方式是正确的,但是TO_TIMESTAMP('2000 JUN', 'FXYYYY MON')会返回错误,因为TO_TIMESTAMP只是期望一个空格。
  • 在TO_CHAR函数中允许使用普通文本,并且函数的输出也是以文本方式实现的。
  • 在把字符串从类型timestamp 转换到date的过程中,如果这里有YYY,YYYY,或者是Y,YYY字段,那么CC字段可以忽略。如果使用了带有YY或者Y字段的CC值,那么年的计算方式就是(CC-1)*100+YY。
下面的表格显示了格式化数值时可以使用的模板模式。
表 4. Template Patterns for Numeric Formatting
Pattern Description
9 Value with the specified number of digits
0 Value with leading zeroes
. (period) Decimal point
, (comma) Group (thousand) separator
$ Dollar sign
PR Negative value in angle brackets
S Sign anchored to number (uses locale)
L Currency symbol (uses locale)
D Decimal point (uses locale)
G Group separator (uses locale)
MI Minus sign specified in right-most position (if number < 0)
RN or rn Roman numeral (input between 1 and 3999)
V Shift specified number of digits (see notes)
说明
  • 数字9产生的值和9s的一样,如果没有指定数字的话,那么输出空格。
  • TH不转换小于0的值,并且不转换小数值。

V有效地用10n 和输入值相乘,其中n是V.TO_CHAR后面数字的数量。在这里不支持使用V和小数点组 合(例如,不允许使用99.9V99这种形式)。

下面的表显示了一些使用TO_CHAR和TO_DATE函数的示例。
Expression Result
TO CHAR(CURRENT TIMESTAMP, 'Day, DD HH12:MI:SS') 'Tuesday , 06 05:39:18'
TO CHAR(CURRENT TIMESTAMP, 'FMDay, FMDD HH12:MI:SS') 'Tuesday, 6 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(1485, '9,999') ' 1,485'
TO CHAR(1485, '9G999') ' 1,485'
TO CHAR(148.5, '999.999') ' 148.500'
TO CHAR(148.5, 'FM999.999') '148.5'
TO CHAR(148.5, 'FM999.990') '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(4 85, 'FM999MI') '485'
TO CHAR(-485, '999PR') '<485>'
TO CHAR(485, 'L999') '$ 485'
TO CHAR(4 85, 'RN') ' CDLXXXV'
TO CHAR(4 85, 'FMRN') 'CDLXXXV'
TO CHAR(5.2, 'FMRN') 'V'
TO CHAR(12, '99V999') ' 12000'
TO CHAR(12.4, '99V999') ' 12400'
TO CHAR(12.45, '99V9') ' 125'