Shulou(Shulou.com)06/01 Report--

This article mainly shows you "what are the conversion functions in Oracle", the content is easy to understand, clear, hope to help you solve your doubts, the following let the editor lead you to study and learn "what are the conversion functions in Oracle" this article.

Conversion function TO_CHAR function converts data to characters

The TO_CHAR function returns the value of the VARCHAR2 data type. TO_CHAR (num1 [, format mask [, nls_parameters]]) when applying it to a value of the NUMBER data type

The num parameter is mandatory and must be a numeric value. The optional format parameter is used to specify numeric format information-such as width, currency symbols, the location of the decimal point, and group (or thousand) delimiters, which must be enclosed in single quotation marks. In addition, there are other options for formatting information for numbers to be converted to characters.

Select to_char (00001) from dual

Select to_char (00001) from dual

The TO_CHAR function converts data to characters

Format element

Element description

Format

Figures

Character result

nine

Digital width

9999

twelve

twelve

0

Show the previous 0

09999

0012

00012

.

The position of the decimal point

09999.999

030.40

00030.400

D

The position of the decimal separator (the default is the nominal point)

09999D999

030.40

00030.400

The position of the comma

09999999

03040

00003040

G

Position of the component delimiter (default is comma)

09999G999

03040

00003040

$

dollar

$099999

03040

$003040

L

Local currency

L099999

03040

GBP003040 (if nls_currency is set to GBP)

MI

Represents the position of the minus sign of a negative number

99999MI

-3040

3040-

PR

Negative numbers enclosed in parentheses

99999PR

-3040

EEEE

Scientific counting method

99.99999EEEE

121.976

1.21976E+02

U

Nls_dual_currency

U099999

03040

CAD003040 (if nls_dual_currency is set to CAD)

V

Multiplied by 10n times (n is the number of 9 after V)

9999V99

3040

304000

S

Preceded by + or-

S999999

3040

+ 3040

The TO_CHAR function converts dates to characters

Using the TO_CHAR function, you can convert DATE items into character representations of almost all dates using a variety of format models.

Syntax: TO_CHAR (date1 [, format [, nls_parameter])

Only the date1 parameter is mandatory, and date1 must be a value that can be implicitly converted to a date. The optional format parameter is case-sensitive and must be enclosed in single quotation marks. The format mask specifies which date elements, whether to describe the element with a long name or an abbreviation. The names of the day and month are also automatically filled in blanks. You can remove these spaces using the modifier of the format mask, called the fill pattern (fm) operator. Adding the letter fm before the format model commands Oracle to remove all spaces from the names of the day and month. There are many formatting options for dates that are converted to strings.

Select to_char (sysdate) | |'is today''s date' from dual

Select to_char (sysdate,'Month') | |'is special time 'from dual

Select to_char (sysdate,'fmMonth') | |'is special time 'from dual

To_char converts a date to a string

Suppose the format element acts on the date 02-JUN-1975

Format element

Description

Result

Y

The last person of the year

five

YY

The last two of the year

seventy-five

YYY

The last three of the year

nine hundred and seventy five

YYYY

Year represented by four digits

1975

RR

The year represented by two numbers (known century)

seventy-five

YEAR,year,Year

The year of case sensitivity and spelling in English

NINETEEN SEVENTY FIVE

Nineteen seventy five

Nineteen Seventy Five

MM

A double-digit month

06

MON,mon,Mon

The three-letter acronym of month

JUN,jun,Jun

MONTH,month,Month

A month that is case sensitive and spelled in English

JUNE,june,June

D

The day of the week

two

DD

The double-digit day of the month

02

DDD

The day of the year

one hundred and fifty three

DY,dy,Dy

The three-letter abbreviation of the week

MON,mon,Mon

DAY,day,Day

A week that is case-sensitive and spelled in English

MONDAY,Monday,Monday

Extract the time portion of the date-time data type, with the date used in the table as 27-JUN-2010 21:35:13

Format element

Description

Result

AM,PM,A.M. And P.M.

Meridian indicator

PM

HH, HH2 and HH24

Hours of the day, 1-12:00 and 0-23:00

09,09,21

MI

Score (059)

thirty-five

SS

Seconds (059)

thirteen

SSSSS

Seconds after midnight (0,86399)

77713

Other elements that can be used in the date-time format model. Punctuation marks are used to separate format elements. There are three types of suffixes that format the components of a date-time element. Also, if you enclose character literals in double quotes, you can include them in the return value. Use date 12/SEP/08 14:31

Format element

Description and format mask

Result

/. ,? #!-

Punctuation mark: 'MM.YY'

09.08

"any character literal"

Character literal:'"Week" W "of" Month'

Week 2 of September

TH

Positional or ordinal text: 'DDth "of" Month'

12TH of September

SP

Spell out the number: 'MmSP month Yyyysp'

Nine September Two Thousand Eight

THSP or SPTH

Spell out the position or ordinal number: 'hh34SpTh'

Fourteenth

Use the TO_DATE function to convert characters to dates

The TO_DATE function returns a value of type DATE. A string converted to a date may contain all or part of the date-time elements that make up the DATE. When converting only strings that contain a subset of date-time elements, Oracle provides fund defaults to construct the complete date. The components of a string are associated with different date-time elements through a format model or mask.

Syntax: TO_DATE (string1 [, format, [nls_parameter]])

Only the string1 parameter is mandatory, and if no format mask is provided, string1 is implicitly converted to a date. Almost always use the optional fromat parameter and specify it in single quotes, the same format mask as TO_CHAR. The TO_DATE function is decorated with fx, which means that the string1 and the format mask must exactly match, otherwise an error will be reported.

Select to_date ('25murDECMel 2010') from dual

Select to_date ('25murDEC') from dual;-error

Select to_date ('25muri DECS from dual from dual

Select to_date ('25-DEC-2010 18 HH24:MI:SS' 03 HH24:MI:SS' 45) from dual

Select to_date ('25ripDECMYYY') from dual;-- error

The TO_NUMBER function converts characters to numbers

The TO_NUMBER function returns a value of type NUMBER. Strings converted to numbers must be in the appropriate format in order to convert or delete all non-numeric components with the appropriate format mask.

Syntax: TO_NUMBER (string1 [, format, [nls_parameter]])

Only the string1 parameter is mandatory, and if no format mask is provided, string1 must be a value that can be implicitly converted to a number. Specify optional format parameters in single quotation marks. The same format mask as TO_CHAR converts numbers into strings.

Select to_number ('$1000.55') from dual;-- error

Select to_number ('$01000.55) from dual

Note: the TO_NUMBER function converts character items into numbers. If you convert a number using a shorter format mask, an error is returned, and if you convert a number using a longer format mask, the original number is returned.

These are all the contents of the article "what are the conversion functions in Oracle?" Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.