How to use Oracle to_char function, oracleto_char

How to use Oracle to_char function, oracleto_char

The function of the Oracle to_char function is to convert the numeric or date type to the numeric type. The following describes the use of the Oracle to_char function in detail, hoping to help you.

Postgres Formatting Function provides a set of effective tools for various data types (date/time, int, float, numeric) convert to formatted string, and convert from formatted string to original data type in turn.
Note: The second parameter of all formatting functions is the template used for conversion.

Table 5-7. Formatting Functions

Function	Return	Description	Example
To_char(Timestamp, text)	Text	Convert timestamp to string	To_char(Timestamp 'right', 'hh12: MI: ss ')
To_char(Int, text)	Text	Convert int4/int8 to string	To_char(125, '123 ')
To_char(Float, text)	Text	Convert float4/float8 to string	To_char(125.8, '999d9 ')
To_char(Numeric, text)	Text	Convert numeric to string	To_char(Numeric '-125.8', '999d99s ')
To_date (text, text)	Date	Convert string to date	To_date ('05 Dec 2000 ', 'dd Mon yyyy ')
To_timestamp (text, text)	Date	Convert string to timestamp	To_timestamp ('05 Dec 2000 ', 'dd Mon yyyy ')
To_number (text, text)	Numeric	Convert string to numeric	To_number ('1970-', '99g999d9s ')

Table 5-8. templates for date/time Conversion

Template	Description
HH	Hours per day (01-12)
HH12	Hours per day (01-12)
HH24	Hours per day (00-23)
MI	Minute (00-59)
SS	Seconds (00-59)
SSSS	Seconds after midnight (0-86399)
AM or A.M. or PM or P. M.	Midday logo (uppercase)
Am or a.m. or pm or p. m.	Midday logo (lower case)
Y, YYY	Year with commas)
YYYY	Year (4 and more)
YYY	The last three digits of the year
YY	The last two
Y	The last digit of the year
BC or B .C. or AD or A.D.	Year ID (uppercase)
Bc or B .c. or ad or a.d.	Year ID (lower case)
MONTH	Full-length uppercase month name (9 characters)
Month	Full-length mixed-case month name (9 characters)
Month	Full-length lowercase month name (9 characters)
MON	Abbreviated month name (3 characters)
Mon	Abbreviation: Mixed-case month name (3 characters)
Mon	Lowercase abbreviation month name (3 characters)
MM	Month (01-12)
DAY	Full-length capital Date name (9 characters)
Day	Full-length mixed-case Date name (9 characters)
Day	Full-length lowercase Date name (9 characters)
DY	Abbreviation: Capital Date name (3 characters)
Dy	Abbreviation: Mixed-case Date name (3 characters)
Dy	Lowercase Date name (3 characters)
DDD	Days in a year (001-366)
DD	Days in a month (01-31)
D	Days in a week (1-7; SUN = 1)
W	The number of weeks in a month
WW	The number of weeks in a year
CC	Century (second place)
J	Julian Date (from January 1, January 1, 4712 BC)
Q	Quarter
RM	The month of the Roman numerals (I-XII; I = JAN)-UPPERCASE
Rm	Month of the Roman numerals (I-XII; I = JAN)-lower case

All templates allow the use of prefix and suffix modifiers. The modifier is always allowed in the template. Prefix'FX'Is just a global modifier.

Table 5-9. Used for date/time templatesTo_char() Suffix

Suffix	Description	Example
FM	Fill mode prefix	FMMonth
TH	Suffix of numbers in uppercase order	DDTH
Th	Suffix of lowercase sequence numbers	DDTH
FX	Fixed mode global options (see below)	FX Month DD Day
SP	Spelling mode (not implemented yet)	DDSP

Usage instructions:

• If notFXOption,To_timestampAndTo_dateIgnore blank.FXMust be the first entry declaration in the template.
• The backslash ("\") must be used as a double backslash ("\"), for example, '\ HH \ MI \ SS '.
• Strings between double quotation marks ('"') are ignored and not analyzed. If you want to write double quotation marks to the output, you must place a double backslash ('\'), For example, '\ "YYYY Month \\"'.
• To_charText without leading double quotation marks ('"') is supported, but any strings between double quotation marks will be processed quickly and will not be interpreted as template keywords (for example:'"Hello Year:" YYYY').

Table 5-10. UsedTo_char(Numeric).

Template	Description
9	Value with the specified number of digits
0	Value of leading zero
. (Period)	DecimalPoint
, (Comma)	Group (thousands) Separator
PR	Negative value in angle brackets
S	Negative value with negative sign (use localization)
L	Currency symbols (use localization)
D	DecimalPoint (use localization)
G	Group separator (use localization)
MI	Negative number at the specified position (if the number is <0)
PL	The positive number at the specified position (if the number is greater than 0)
SG	Positive/negative signs at the specified position
RN	Roman numerals (input between 1 and 3999)
TH or th	Convert to ordinal number
V	MobileNBit (Decimal) (See annotations)
EEEE	Scientific notation. Not Supported now.

Usage instructions:

• The signed words using 'sg ', 'pl', or 'mi 'are not attached to a number. For example,To_char(-12, 's9999') generate'-12', AndTo_char(-12, 'mi999999 ') generated'-12'.OracleIs not allowed in9Previously UsedMI, But requires9InMIFront.
• PL,SG, AndTHIs the ipvs extension.
• 9Indicates9The same number of digits in the string. If no number is available, use a blank space ).
• THDo not convert or convert a value smaller than zeroDecimal.THIs an ipvs extension.
• VMultiply the input value conveniently10 ^N, HereNIs followedVThe following number.To_charDo not supportVWithDecimalPoint binding (for example,. "99.9V99" is not allowed ).

Table 5-11.To_charExample

Input	Output
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 ')	'123'
To_char(12, '192. 9 ')	'123'
To_char(12, 'fm9990999. 9 ')	'123'
To_char(485, '123 ')	'123'
To_char(-485, '123 ')	'-485'
To_char(485, '9 9 9 ')	'48 5'
To_char(1485, '123 ')	'123'
To_char(1485, '9g999 ')	'1 100'
To_char(148.5, '192. 123 ')	'123'
To_char(148.5, '999d999 ')	'123'
To_char(3148.5, '9g999d999 ')	& Apos; 3 & apos; 148,500 & apos'
To_char(-485, '999s ')	'2017 -'
To_char(-485, '999mi ')	'2017 -'
To_char(485, '999mi ')	'123'
To_char(485, 'pl999 ')	'+ 123'
To_char(485, 'sg999 ')	'+ 123'
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: 8080'
To_char(485.8, '"Pre-decimal:" 999 "Post-decimal:". 999 ')	'Pre-decimal: 485 Post-decimal:. 123'
To_char(12, '99v999 ')	'123'
To_char(12.4, '99v999 ')	'123'
To_char(12.45, '99v9 ')	'123'

The simplest application of Oracle to_char functions:

/* 1.0123 ---> '1. 0123 '*/
Select TO_CHAR (1.0123) FROM DUAL
/* 123 ---> '123 '*/
Select TO_CHAR (123) FROM DUAL

Next let's take a look at the following:

/* 0.123 ---> '. 100 '*/
SELEC TO_CHAR (0.123) FROM DUAL

The above result '. 100' is not what we want in most cases. What we want is '0. 100 '.

Let's take a look at the specific usage of the to_char function:

TO_CHAR (n [, fmt [, 'nlsparam'])

The Oracle to_char function converts n of the NUMBER type into a value of the VARCHAR2 type in the numerical format fmt. 'Nlsparams' specifies the characters returned by the element in numerical format, including:

. Decimal point character
. Group Separator
. Local coin symbol
. International coin symbol

The form of Yuan change is:

'Nls _ NUMERIC_CHARACTERS = "dg" NLS_CURRENCY = "tcxt" NLS_ISO_CURRENCY = territory'

D indicates the decimal point, and g indicates the group separator.

Example: TO_CHAR (17145, 'l099g999', 'nls _ NUMERIC_CHARACTERS = ".," NLS_CURRENCY = "NUD" ') = NUD017, 145
Based on the above understanding, and then look at some fmt formats, we can use the following expression to get the value of '0. 123:

/* 0.123 ---> '123 '*/
Select TO_CHAR (0.123, '0. 999 ') FROM DUAL
/* 100.12 ---> '######'*/
Select TO_CHAR (100.12, '0. 999 ') FROM DUAL
/* 1.12 ---> '123 '*/
Select TO_CHAR (1.12, '0. 999 ') FROM DUAL
'123' is displayed, but there is a space in front.

The value of 100.12 is ######, and the value of '1. 12' is changed to '1. 120 '.

Let's re-determine a new requirement:

1. Remove Space
2. A maximum of four decimal places, with at least two digits retained.

1 ---> '1. 00'; 1.1 ---> '1. 00'; 1.12 --> '1. 12'; 1.1234 ---> '1. 100 ';

1.12345 ---> '1. 1235'

The final implementation is as follows:

/*
FM: Except Space
9999999.0099: the maximum number of digits to the left of the decimal point is 7 digits, and the number to the right of the decimal point is at least 2 digits. The maximum number is 4 digits, and the number is rounded to 5th digits.
*/
Select TO_CHAR (123.0233, 'fm9999999. 0099 ') FROM DUAL

Use of oracle to_char Functions

SQL> alter session set nls_date_format = 'yyyy-mm-dd hh24: mi: ss ';

Session altered.

SQL> select sysdate, to_char (CURRENT_TIMESTAMP, 'dd-MON-YYYY HH24: MI: SS. ff3') from dual;

SYSDATE TO_CHAR (CURRENT_TIMESTAMP, 'dd-
-------------------------------------------------
18:10:36 13-NOV-2006 18:10:36. 558

Oracle to_char syntax

TO_CHAR converts a date or number to a string and cannot specify the string length.
Use the TO_CHAR function to process the date:
TO_CHAR (number, 'format') for example: TO_CHAR (salary, '$99,999.99 ')
Use the TO_CHAR function to process the date:
TO_CHAR (date, 'format'); TO_CHAR (newdate, 'yyyy-mm-dd ')

The length of the specified string can be lpad, rpad, or substring:
Lpad (field name, fill length, fill character) left fill
Rpad (field name, fill length, fill character) Right fill
Substr (string, start_position, [length]) gets the string with the specified start position and length.