How to use Oracle To_char function _oracle

The function of the Oracle To_char function is to convert a numeric or date type to a character type, and here's a detailed description of the use of the Oracle To_char function, which I hope will help you.

The Postgres Format function provides an effective set of tools for converting various data types (date/time, int,float,numeric) into formatted strings and, conversely, converting from formatted strings to original data types.
Note: The second parameter of all formatting functions is the template for conversion.

table 5-7. Formatting functions

function	return	Description	Example
To_char (timestamp, text)	Text	Convert Timestamp to String	To_char (timestamp ' Now ', ' HH12:MI:SS ')
To_char (int, text)	Text	Convert Int4/int8 to String	To_char (125, ' 999 ')
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	Converts a string to date	To_date (' Dec ', ' DD Mon YYYY ')
To_timestamp (text, text)	Date	Convert String to Timestamp	To_timestamp (' Dec ', ' DD Mon YYYY ')
To_number (text, text)	Numeric	Convert string to Numeric	To_number (' 12,454.8-', ' 99g999d9s ')

table 5-8. Templates for Date/time conversions

Templates	Description
HH	Number of hours in a day (01-12)
HH12	Number of hours in a day (01-12)
HH24	Number of hours in a day (00-23)
MI	Minutes (00-59)
Ss	Seconds (00-59)
SSSS	Seconds after midnight (0-86399)
AM or a.m. or PM or p.m.	Noon Identification (upper case)
Am or a.m. or PM or p.m.	Noon sign (lowercase)
Y,yyy	Years with commas (4 and more bits)
YYYY	Years (4 and more bits)
YYY	The latter three digits of the year
Yy	The latter two of the year
Y	The last one 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 (lowercase)
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	Uppercase abbreviation month name (3 characters)
Mon	Abbreviation mixed Case month name (3 characters)
Mon	Lowercase abbreviated month name (3 characters)
Mm	Month (01-12)
Day	Full length uppercase 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	Abbreviated Lowercase date name (3 characters)
Ddd	Days of the Year (001-366)
Dd	One months of the day (01-31)
D	The days of the week (1-7;sun=1)
W	Weeks in one months
Ww	The number of weeks in a year
Cc	Century (2-bit)
J	Julian Date (date since January 1, 4712 BC)
Q	Quarter
Rm	Roman Numeral Month (I-xii;i=jan)-Uppercase
Rm	Roman Numeral Month (I-xii;i=jan)-lowercase

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

table 5-9. Suffix for date/time template to_char()

suffix	Description	Example
Fm	Fill mode prefix	Fmmonth
TH	Uppercase Order number suffix	Ddth
Th	lowercase ordinal number suffix	Ddth
Fx	Fixed mode global options (see below)	FX Month DD Day
Sp	Spelling mode (not yet implemented)	Ddsp

Usage notes:

• If you do not use the FX option,to_timestamp and to_date ignore whitespace. FX must be the first entry declaration in the template.
• A backslash ("\") must be used as a double backslash ("\"), such as ' \\HH\\MI\\SS '.
• The string between the double quotes (' ") is ignored and is not parsed. If you want to write double quotes to the output, you have to place a double backslash (' \ \ ') in front of the double quotes, such as ' \ \ YYYY month\\'.
• To_char supports text with no leading double quotes (' "), but any string between double quotes is processed quickly and is guaranteed not to be interpreted as a template keyword (for example:' Hello year: ' YYYY ').

table 5-10. Templates for To_char(numeric)

Templates	Description
9	Value with a specified number of digits
0	Value leading to zero
. Period	decimal Point
, (comma)	Grouping (Thousand) separator
PR	Negative in angle brackets
S	Negative value with minus sign (using localized)
L	Currency symbol (using localization)
D	decimal points (using localization)
G	grouping delimiters (using localization)
MI	Minus sign at specified position (if number < 0)
Pl	A plus sign at the indicated position (if number > 0)
SG	The positive/negative sign at the specified position
Rn	Roman numerals (input between 1 and 3999)
th or th	Convert to Ordinal
V	Move n bit ( decimal ) (see note)
Eeee	Scientific count. Now not supported.

Usage notes:

• A signed word using ' SG ', ' PL ' or ' MI ' is not attached to the number; For example,to_char( -12, ' S9999 ') generates ' -12 'and to_char( -12, ' MI9999 ') ) to generate '-a '. Implementation in Oracle does not allow mito be used in front of 9 , but requires 9 in front of mi .
• PL,SG, and TH are Postgres extensions.
• 9 indicates an equal number of digits in the 9 string. If there are no digits available, use a blank (space).
• TH does not convert a value less than 0, nor does it convert a decimal number. TH is an Postgres extension.
• v conveniently multiply the input value by 10^n, where n is the number following the v . To_char does not support the use of tying V to a decimal point (for example. "99.9v99" is not allowed.

table 5-11. To_char Example

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 ')	' 0.1 '
To_char (12, ' 9990999.9 ')	' 0012.0 '
To_char (The ' 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 (The ' 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 ' *
Select To_char (123) from DUAL

Next look at the following:

/*0.123---> '. 123 ' *
Selec To_char (0.123) from DUAL

The result '. 123 ' In most cases is not the result we want, we want ' 0.123 '.

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 number type to a value of VARCHAR2 type in numeric format FMT. ' Nlsparams ' specifies the characters returned by elements in numeric format, including:

． Decimal decimal character
． Group Separator
． Local coin symbol
． International coin Symbol

The form of the variable is:

"Nls_numeric_characters=" DG "nls_currency=" TCXT "Nls_iso_currency=territory"

where D is a decimal character and G is a group separator.

Example: To_char (17145, ' l099g999 ', ' nls_numeric_characters= '., "nls_currency=" NUD ") =nud017,145
With the above understanding, and then look at some of the FMT format, we can use the following expression to get the value of ' 0.123 ':

/*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 ' is out, but there is one more space in front.

The value for 100.12 is ######, and the value of ' 1.12 ' turns to ' 1.120 '.

We re-identify a new requirement:

1, to the space
2, the decimal point is up to 4 digits, at least 2 digits.

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
9999999.0099: Allow the maximum positive number to the left of the decimal point is 7 digits, the right decimal point to a minimum of 2 digits, up to 4 digits, and rounding at 5th digits
*/
Select to_char (123.0233, ' FM9999999.0099 ') from DUAL