PL/SQL format conversion

TO_NUMBER (char [, 'format _ model']) character to numeric type
TO_DATE (char [, 'format _ model']) character conversion to date type
Format description: the format of the string to be converted from the front must be the same as that of the string to be converted from the Front (matching problem: Format and number of digits ).
TO_CHAR (date [, 'format _ model' [, nlsparams])
The second parameter can be omitted. If the format is not specified, it is output in the system default format.
Case sensitive.
Use the FM symbol (added before the format Controller) to remove spaces or zero at the first place.
If NLSPARAMS is specified, it controls the language used to return the month and day components of the string. Format:
'Nls _ DATA_LANGUAGE = linguistic ', which indicates the required language.
Example:
Select to_char (sysdate, 'fmyyyy-mm-dd') from dual;
Type of the format controller:
YYYY four-digit year
YEAR spelling
MM 2-digit month
Full name of MONTH
The first three characters of the MON month name
The first three characters of DY's day name
Full name of DAY week name
DD 2-digit day
Time Format controller:
HH24: MI: SS AM
HH12: MI: SS PM
"" Is used to control the format of special characters.
SELECT TO_CHAR (SYSDATE, 'fmyyyy "year" mm "month" dd "day" ') from dual;
DDSPTH
~~
DD is the format controller.
TH is the ordinal word, which converts a date into an English ordinal word spelling.
SP is a base number word that converts a date into a base number spelling in English.
TO_CHAR (NUM [, 'format _ model' [, nlsparams]) to convert numbers
Convert the NUMBER type parameter NUM to the VARCHAR2 type. If the FORMAT is specified, it controls the entire conversion.
If no FORMAT is specified, the result string contains the same characters as the number of valid bits in NUM. NLSPARAMS is used to specify the decimal point, the kilobytes, and the currency symbol. The format can be: 'nls _ NUMERIC_CHARS = ''dg'' NLS _ CURRENCY = ''string '''
D and g represent decimal points and kilobytes. STRING indicates the currency symbol.
Digit format controller:
9 represents a digit (replacement character. Yes, numbers are displayed; No. .)
0 indicates a number (with a number, displayed; no, forced to show 0 .)
$ Circle symbol
L local currency
. Decimal point
, Kilobytes
B. When the integer part is 0, fill the integer part with spaces. Example: B999
MI returns a negative value with a successor symbol instead of a leading negative number. The positive value carries a trailing space. 999MI
S returns a leading or successor symbol. The positive number is +, and the negative number is -. S9999 or 9999 S
PR returns a negative number using angle brackets. A positive number contains leading or trailing spaces. 999PR
D. Return a decimal point at the specified position. The number of 9 on both sides specifies the maximum number of digits. 99D9
G returns a kilobytes of characters at a specified position. G can appear multiple times in FORMAT_model. 9G999G9
C returns the ISO currency symbol at the specified position. C can appear multiple times in FORMAT_model. C99
L return the local currency symbol at the specified position. L99
V returns a value multiplied by the N power of 10. Here N is the number of 9 after V. 99V99
EEEE returns this value using the scientific notation. 9.99 EEEE
RM uses an upper-case roman number to return the value. RM
Rm uses a lower-case roman number to return the value. Rm
FM returns a value without leading or trailing spaces. FM99.09
The NUMBER of digits of the control operator must be greater than or equal to the NUMBER.