PL and SQL type format conversion

  • 2020-05-27 07:26:07
  • OfStack

TO_NUMBER(char[,'format_model']) character converts to numeric type
The TO_DATE(char[,'format_model']) character is converted to a date type
Format specifier: converts to the same format as the preceding string to be converted (matching problem: format and bits).
The second parameter can be omitted without specifying the format and output in the system default format.
Case sensitive.
Use the FM symbol (added before the format control) to remove Spaces or the first zero.
If NLSPARAMS is specified, it controls the language in which the month and day components of the returned string are used. Format for:
'NLS_DATA_LANGUAGE=language',language means the required language.
Ex. :
select to_char(sysdate,'FMyyyy-mm-dd') from dual;
Type of format controller:
YYYY 4 years
YEAR spelling
MM 2 digit month
Full name of month MONTH
The first three characters of the month name MON
The first three characters of DY
DAY full name of the week
DD 2 bit days
Time format control:
Use "" to implement a format control character with special characters.
SELECT TO_CHAR(SYSDATE,'FMyyyy" year "mm" month "dd" day ") from dual;
DD is a format control.
TH is an ordinal number, which translates the date into an English ordinal spelling.
SP is the cardinal word, which translates the date into the English cardinal spelling.
TO_CHAR(NUM[,'format_model'[,nlsparams]]) converts the number
Converts the NUMBER type parameter NUM to VARCHAR2. If you specify FORMAT, it controls the entire conversion.
If FORMAT is not specified, the result string will contain the same number of characters as the number of valid bits in NUM. NLSPARAMS is used to specify decimal points and thousands and currency symbols. It can be formatted as: 'NLS_NUMERIC_CHARS=' dg ' 'NLS_CURRENCY= 'string'
d and g represent the decimal point and the thousandth, respectively. STRING stands for currency symbol.
Numeric format control character:
9 represents a one-digit number (substitution). Yes, digital display; No. Not everything.)
0 represents a digit number (with digits, display; No, force 0.)
Dollar sign
L local currency
The decimal point
, micrometer
B fills the integer part as a space when the integer part is 0. Example: B999
MI returns a negative value with a successor symbol instead of a leading minus, and a positive value will have a successor space. 999 MI
S returns a leading or successor symbol with a positive plus and a negative minus. S S9999 or 9999
PR USES Angle brackets to return negative Numbers. Positive Numbers will have leading or following Spaces. 999 PR
D returns a decimal point at the specified location. The number of 9's on both sides specifies the largest number of digits. 99 D9
G returns the thousandth at the specified location, and G can appear multiple times in FORMAT_model. 9 G999G9
C returns the ISO currency symbol at the specified location. C can appear multiple times in FORMAT_model. C99
L returns the local currency symbol at the specified location. L99
V returns a number multiplied by 10 to the power N, where N is the number of 9 after V. 99 V99
EEEE returns this value using scientific notation. 9.99 EEEE
RM returns this value using a capital Roman numeral. RM
rm returns this value using lowercase Roman numerals. rm
FM returns a value without leading and following Spaces. FM99. 09
The format control digit 1 must be greater than or equal to NUMBER, not less than.

Related articles: