Shulou(Shulou.com)05/31 Report--

Editor to share with you what are the common functions of Oracle, I believe most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

1. Case conversion function LOWER

The LOWER function replaces uppercase characters in a string with lowercase. Grammar: LOWER (s).

Select lower (100mm 100), lower ('SQL'), lower (sysdate) from dual

UPPER

The UPPER function replaces lowercase characters in a string with uppercase. Grammar: UPPER (s).

Select upper ('sql') from dual

INITCAP

The INITCAP function converts a string to uppercase. The first letter of each word in the string is converted to uppercase, and the remaining letters of each word are converted to lowercase. Words are separated by spaces or underscores, but some characters (such as percentage sign (%), exclamation point (!) Or the dollar sign ($) is also a valid word separator. Punctuation marks and special characters are also valid word separators. Syntax: INITCAP (s)

Select initcap ('initcap or init_cat or init%cap') from dual

2. Character manipulation function CONCAT

The CONCAT function concatenates two character literals, columns, or expressions to generate a larger word character expression. Grammar: CONCAT (S1 and S2)

Select concat ('Today is:',SYSDATE) FROM DUAL

CONCAT can only use two parameters

Select concat ('Outer1', concat ('Inner1',' Inner2')) from dual

LENGTH

The LENGTH function returns the number of characters that make up the string. Spaces, tabs, and special characters are counted by the LENGTH function. There is only one parameter, LENGTH (s). (tabulator operator 1)

Select length ('ab cd e') from dual

LPAD and RPAD

The LPAD (RPAD) function returns a composite string formed after the left (right) side of a given string is filled with a specified number of characters. The strings used for padding include character literals, column values, expressions, spaces (default), tabs, and special characters.

The LPAD and RPAD functions take three arguments, and the syntax is RPAD (sjournal njournal p) and LPAD (sjournal njournal p). S represents the source string, n represents the final length of the returned string, and p specifies the string to be populated.

Select LPAD ('abc',6,'*'), RPAD (' abc',6,'*') from dual

TRIM

The TRIM function removes some characters from the beginning or end of the character value, generating a shorter character entry from the face.

The arguments used by the TRIM function consist of a mandatory component and an optional component. Syntax: TRIM ([trailing | leading | both] trimstring from s). The trimmed string (s) is mandatory. Only one correction character can be specified.

TRIM (s) removes spaces on both sides of the input string.

TRIM (trailing trimstring from s) removes all trimstring, if any, from the end of the string s.

TRIM (leading trimstring from s) removes all trimstring, if any, from the beginning of the string s.

TRIM (both trimstring from s) removes all trimstring, if any, from the beginning and end of the string s.

Select trim (both'* from'* Hidden*'), trim (leading'* 'from' * Hidden*'), trim (trailing'* 'from' * Hidden*'), trim (both from 'Hidden'), trim (trailing from' Hidden'), trim (leading from 'Hidden') from dual

RTRIM

The RTRIM function removes some characters from the end of the character value, resulting in a shorter string.

Syntax: RTRIM (string [, trimstring]), the trimmed string is required and multiple characters can be deleted. Spaces are deleted by default.

Select rtrim ('abcd') from dual

Select rtrim ('abcd***','*') from dual; select rtrim (' abcd*#','*#') from dual

LTRIM

The LTRIM function removes some characters from the beginning of the character value, resulting in a shorter string.

Syntax: RTRIM (string [, trimstring]), the trimmed string is required and multiple characters can be deleted. Spaces are deleted by default.

Select ltrim ('abcd') from dual

Select ltrim ('* abcd','*') from dual; select ltrim ('* # abcd','*#') from dual

INSTR

The INSTR function determines the position of the search string within a given string. It returns the numeric position where the search string begins to appear for the nth time (relative to the specified starting position). Returns 0. 0 if the search string does not exist.

The INSTR function takes two optional parameters and two mandatory parameters. Syntax: INSTR (source string,search string, [search start position], [nth occurrence]). The default value for search start position is 1 or the beginning of source string. The default value for nth occurrence is 1 or appears for the first time.

Select instr ('1, 3, 5, 7, 9, 5, 7, 9, 5, 7, 9, 5, 7, 9, 5, 5, 7, 9, 5, 5, 7, 9, 5, 3, 5, 7, 9, 5, 3, 5, 7, 9, 5, 5, 7, 9, 5, 5, 7, 9, 5, 5, 9, 5, 5, 7, 9, 5, 5, 5, 9, 5, 5, 9, 5, 5, 9, 5, 5, 9, 5, 5, 9, 5, 5, 9, 5, 5, 9, 5, 5, 9, 5, 5, 9, 5, 5, 9, 5, 5, 9, 5, 5, 9, 5, 5, 9, 5, 5, 9, 5, 5, 9, 5, 5, 7, 9, 5, 5

From the first character on the left to the right, return to the position where'# 'first appeared.

Select instr ('1, 3, 5, 7, 9, 5, 3, 5, 7, 9, 7, 9, 5, 5, 5, 5, 5, 3, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5

From the fifth character on the left to the right, return to the position where'# 'first appeared.

Select instr ('1, 3, 5, 7, 9, 5, 7, 9, 7, 9, 5, 5, 7, 9, 5, 5, 5, 5, 7, 9, 5, 5, 5, 5, 7, 9, 5, 5, 5, 5, 5, 7, 9, 5, 5, 5, 5, 7, 9, 4, 5, 5, 5, 7, 9, 4, 5, 5, 5, 7, 9, 5, 5, 5, 7, 9, 5, 5, 5, 7, 9, 5, 5, 5, 7, 9, 5, 5, 5, 7, 9, 5, 5, 7, 9, 5, 5, 7, 9, 5, 5, 7, 9, 5, 5, 7, 9, 5, 5, 7, 9, 5, 5, 5, 7, 9, 5, 7, 9

From the third character on the left to the right, return to the position where'# 'appears for the fourth time.

Select instr ('1, 3, 5, 7, 9, 7, 7, 9, 7, 9, 5, 5, 5, 9, 5, 5, 5, 9, 5, 5, 5, 9, 5, 5, 7, 9, 5, 5, 5, 5, 7, 9, 5, 5, 5, 5, 7, 9, 5, 5, 5, 7, 9, 5, 5, 5, 7, 9, 5, 5, 7, 9, 5, 5, 5, 7, 9, 5, 5, 7, 9, 5, 5, 5, 7, 9, 5, 5, 9, 5, 5, 9, 5, 5, 9, 5, 5, 9, 5, 5, 7, 9, 5, 5, 5, 7, 9, 5, 5, 7, 9, 5, 5, 7, 9, 5, 5, 7, 9

From the third character on the left to the right, return to the position where'# 'appeared for the 10th time, no return 0.

Select instr ('1, 3, 5, 7, 9, 1, 3, 5, 7, 9, 7, 9, 5, 5, 9, 5, 5, 9, 5, 5, 9, 5, 5, 5, 9, 5, 5, 5, 9, 5, 5, 5, 9, 5, 5, 5, 9, 5, 5, 9, 5, 5, 5, 9, 5, 5, 5, 9, 5, 5, 9, 5, 5, 5, 9, 5, 5, 9, 5, 5, 5, 9, 5, 5, 9, 5, 5, 9, 5, 5, 9, 5, 5, 9, 5, 5, 5, 9, 5, 5, 9, 5, 5, 9, 5, 5, 9, 5, 5, 9, 5, 5, 9, 5, 5, 9, 5, 5, 9

From the first character on the right to the left, return to the position where'# 'first appeared.

Select instr ('1, 3, 5, 7, 9, 1, 3, 5, 7, 9, 7, 9, 9, 5, 5, 5, 9, 5, 5, 5, 9, 5, 5, 5, 5, 7, 9, 5, 5, 5, 5, 7, 9, 5, 3, 5, 5, 5, 7, 9, 5, 5, 5, 7, 9, 5, 5, 5, 5, 5, 7, 9, 5, 5, 5, 7, 9, 5, 5, 5, 5, 7, 9, 5, 5, 5, 7, 9, 5, 5, 7, 9, 5, 5, 5, 7, 9, 5, 5, 5, 7, 9, 5, 5, 5, 7, 9, 5, 5, 5, 7, 9, 5, 5, 5, 7, 9, 5, 5

From the first character on the right to the left, return to the position where'# 'appears for the third time.

Select instr ('1, 3, 5, 7, 9, 5, 7, 9, 7, 9, 5, 5, 5, 9, 5, 3, 5, 3, 3, 3, 3, 3, 5, 3, 3, 3, 3, 5, 5, 5, 7, 9, 4, 3, 3, 3, 3, 3, 5, 5, 5, 7, 9, 3, 3, 3, 3, 5, 5, 5, 7, 9, 5, 5, 7, 9, 5, 5, 7, 9, 5, 5, 7, 9, 5, 5, 7, 9, 5, 5, 7, 9, 5, 5, 5, 5, 7, 9, 5, 5, 7, 9, 5, 5, 5, 7, 9, 5, 5, 5, 5, 7, 9, 5, 5, 5, 5, 5, 5

From the third character on the right to the left, return to the position where'# 'appears for the third time.

SUBSTR

The SUBSTR function extracts a string of the specified length, starting at a given position in the source string. If the starting position is greater than the length of the source string, null is returned. If the number of characters extracted from a given starting position is greater than the length of the source string, the returned part is the substring from the starting position to the end of the string.

The SUBSTR function takes three arguments, the first two of which are mandatory. Syntax: SUBSTR (source string,start position, [number of characters to extract]). The default number of characters to extract is the number of characters from start position to the end of source string.

Select substr ('1, 3, 5, 7, 9, 5) from dual

Extract from the fifth character from left to right, from left to right, to the end of the source string.

Select substr ('1, 3, 5, 7, 9, 7, 9, 5, 5, 3, 5, 3, 5, 5, 5, 9, 5, 5, 3, 5, 3, 5, 3, 5, 3, 5, 3, 5, 3, 5, 3, 5, 3, 5, 3, 5, 3, 5, 3, 5, 3, 5, 3, 5, 3, 5, 3, 5, 3, 5, 3, 5, 3, 5, 3, 5, 3, 5, 3, 5, 3, 5, 3, 5, 3, 5, 3, 5, 3, 5, 3, 5, 3, 5, 3, 5, 3, 5, 3, 5, 3, 5, 3, 5, 3, 5, 3, 5, 3, 5, 3, 5, 3, 5, 3, 5, 5, 7, 9

Start with the fifth character from left to right, extract from left to right, and extract 3 characters.

Select substr ('1, 3, 5, 7, 9, 5, 7, 9, 5, 9, 5, 9, 5, 5, 9, 5, 5, 9, 5, 5, 9, 5, 3, 5, 9, 5, 3, 5, 9, 5, 5, 9, 5, 5, 9, 5, 5, 9, 5, 5, 9, 5, 5, 9, 5, 5, 5, 9, 5, 5, 9, 5, 5, 9, 5, 5, 9, 5, 5, 9, 5, 5, 9, 5, 5, 9, 5, 5, 9, 5, 5, 9, 5, 5, 9, 5, 5, 9, 5, 5, 9, 5, 5, 9, 5, 5, 9, 5, 5, 9, 5, 5, 9, 5, 5, 9, 9, 5, 9

Start with the third character from right to left, extract from left to right, and extract 2 characters.

Select substr ('1, 3, 5, 7, 9, 5, 3, 5, 9, 9, 4, 3, 5, 9, 4, 5, 9, 4, 5, 9, 4, 5, 9, 4, 5, 9, 4, 5, 9, 4, 5, 5, 9, 4, 5, 5, 9, 4, 5, 9, 4, 3, 4, 3, 4, 3, 4, 3, 4, 4, 4, 4, 4, 3, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4

Vbl.

REPLACE

The REPLACE function replaces all search items that appear in the source string with substitutes. If the length of the replacement item is different from the length of the search term, the length of the returned string is also different from the length of the source string. If the search string is not found, the source string is returned intact.

The REPLACE function takes three arguments, the first two of which are mandatory. Syntax: REPLACE (source string,search item [, replacement term]) If you omit the replacement term parameter, all occurrences of search item are removed from the source string.

Select replace ('1, 3, 5, 7, 9, 1, 3, 5, 7, 9, 5, 7, 9, 5, 5, 7, 9, 5, 5, 7, 9, 5, 5, 7, 9, 5, 5, 7, 9, 5, 5, 7, 9, 5, 5, 7, 9, 5, 5, 7, 9, 5, 7, 9, 5, 5, 7, 9, 5, 5, 7, 9, 5, 5, 7, 9, 5, 5, 7, 9, 5, 5, 7, 9, 5, 5, 7, 9, 5, 7, 9, 5, 5, 7, 9, 5, 5, 7, 9, 5, 5, 7, 9, 5, 5, 7, 9, 5, 5, 7, 9, 5, 5, 7, 9, 5, 5, 7, 9

Select replace ('1, 3, 5, 7, 9, 5, 7, 9, 5, 7, 9, 5, 7, 9, 5, 5, 7, 9, 5, 5, 7, 9, 5, 3, 5, 7, 9, 5, 3, 5, 7, 9, 5, 5, 7, 9, 5, 5, 7, 9, 5, 5, 7, 9, 5, 5, 7, 9, 5, 5, 5, 9, 5, 5, 9, 5, 5, 7, 9, 5, 5, 5, 9, 5, 5, 9, 5, 5, 9, 5, 5, 9, 5, 5, 9, 5, 5, 9, 5, 5, 7, 9, 5, 5, 7, 9, 5, 5, 7, 9, 5, 5, 7, 9, 5, 5, 7, 9, 5

TRANSLATE III. Numeric function ROUND

The ROUND function rounds numeric values according to the specified decimal precision. Returns a value rounded up or down to the specified decimal precision based on a valid number. If the specified decimal precision is n, the valid data to be rounded is to the right of the decimal point (n = 1). If n is negative, the valid number to be rounded is in n positions to the right of the decimal point. If the data of valid data is greater than or equal to 5, it is rounded up and rounded down in other cases.

The ROUND function takes two parameters. Syntax: ROUND (source number,decimal precision). The source number parameter represents any numeric value. The decimal precision parameter specifies the precision of rounding, which is optional. If the decimal precision parameter is not specified, the default precision of rounding is 0, that is, the source number is rounded to the nearest integer.

Select round (1601.916pr 1) from dual

Select round (1601.916pr 2) from dual

Select round (1601.916) from dual

Select round (1601.916) from dual

Select round (1601.916) from dual

TRUNC

The TRUNC function performs an intercept operation on the data based on the specified decimal precision. Numerical truncation is different from rounding. If the decimal precision is positive, the last value deletes the number according to the specified decimal precision and does not round up or down. However, if the specified decimal precision (n) is negative, the input value returns 0 from the nth digit to the left of the decimal point.

The TRUNC function takes two parameters. Syntax: TRUNC (source number,decimal precision). Source number represents any numeric value. Decimal precision specifies the precision of the intercept, which is optional. If the decimal precision parameter is not specified, the default precision is 0, which means that source number truncates to the nearest integer.

Select trunc (1601.916pr 1) from dual

Select trunc (1601.916pr 2) from dual

Select trunc (1601.916) from dual

Select trunc (1601.916) from dual

Select trunc (1601.916) from dual

MOD

The MOD function returns the remainder of the division operation. Provide two numbers, the divisor and the divisor, and perform the division operation. If the divisor is the divisor, MOD returns 0 because there is no remainder. If the divisor is equal to 0, a no division by zero error is returned, and the MOD function also returns 0. If the divisor is greater than the divisor, the MOD function returns the divisor as a result.

The MOD function takes two parameters. Syntax: MOD (dividend,divisor). Both dividend and divisor parameters can represent numeric literals, columns, or expressions. It can be positive or negative.

Select mod (6 dint 2) from dual

Select mod (5BI 3) from dual

Select mod (7 and 35) from dual

Select mod (5.2) from dual

Select mod (- 5pm 3) from dual

The MOD function is usually used to distinguish between odd and even numbers.

4. Date function SYSDATE

The SYSDATE function takes no arguments and returns the current system date and time of the database server.

Select sysdate from dual

Date operation

Date1-Date2=Num1

You can subtract the log from another date. The difference between these two date items indicates the number of days between them. All numbers, including decimals, can be added to or subtracted from a date entry. In this context, numbers represent the number of days. The sum or difference between a number and a date item always returns a date item. Addition, multiplication, or division of two date items are not allowed.

Select to_date ('31MJANMY01')-to_date (' 01MJANMULY01') from dual

Select sysdate + 1 from dual

MONTHS_BETWEEN

The MONTHS_BETWEEN function returns a numeric value that represents the number of months between two mandatory date parameters. Syntax: MONTHS_BETWEEN (date1,date2). Calculate the difference between date1 and date2 (31 days per month). If date1 adds negative numbers before date2. The difference between these two date parameters may consist of integers and decimals. An integer represents the number of friends between the two dates. The decimal part represents the number of days and time remaining after calculating the integer difference between the year and the month, based on the 31-day month. If the day component of the date to be compared is the same or the last day of the respective month, an integer with no decimal part is returned.

Select months_between (sysdate,sysdate-31) from dual

Select months_between ('29murafure 2008) from dual (' 29murmur2008')

Select months_between ('29Muhammar 2008') * 31 from dual

ADD_MONTHS

The ADD_MONTHS function returns a date entry, which is calculated by adding the specified number of months to a given date.

The ADD_MONTHS function has two mandatory arguments. Syntax: ADD_MONTHS (start date,number of months). The function does not calculate the target date until the specified number of months is added to the start date. The number of months may be negative, so the target date returned is earlier than the start date. Number of months can be a decimal, but it ignores the decimal part and uses the integer part.

Select add_months ('07MAPRMI 2009) from dual

Select add_months ('07Mutual APRMU2009) from dual

Select add_months ('07MAPRMI 2009) from dual (September 2009) APRMI 2009

NEXT_DAY

The date returned by the NEXT_DAY function is the date of the next occurrence of the specified day of the week.

The NEXT_DAY function has two mandatory arguments. Syntax: NEXT_DAY (start date,day of the week). Function calculates the date of the next occurrence of the day of the week parameter after start date. The day of the week parameter can be a character value or an integer value. Acceptable values are determined by the NLS_DATE_LANGUATE database parameter, but the default value is at least the first three characters or integer values of the day name, where 1 is Sunday, 2 is Monday, and so on. In any case, you should specify a character value that represents the day of the week. A short name can be longer than three characters, such as sun, sund, sunda, or sunday.

Select next_day ('01MJANMUE 2009') from dual

Select next_day ('01MJANMUE 2009) from dual

Select next_day ('01MJANMI2009) from dual

LAST_DAY

The LAST_DAY function returns the date of the last day of the month to which the specified day belongs.

The LAST_DAY function has a mandatory argument. Syntax: LAST_DAY (start date). This function extracts the month to which the start date parameter belongs and calculates the date of the last day of the month.

Select LAST_DAY ('01MJANMUL 2009') from dual

Date ROUND

The date ROUND function rounds the value according to the specified date precision format. The returned value is either rounded up or down to the nearest date precision format.

The date ROUND function takes a mandatory parameter and an optional parameter. Syntax: ROUND (source date [, date precision format]) The source date parameter represents any date item. The date precision format parameter specifies the precision of rounding, which is optional, and if not specified, the default rounding precision is day. Date precision formats includes century (CC), year (YYYY), quarter (Q), month (MM), week (W), day (DD), hour (HH) and minute (MI).

The upward introduction of the century is equivalent to adding one century to the current century. If the day part is greater than 16, it will be rounded up to the next month, otherwise it will be rounded down to the beginning of the month. If the month is between 1 and 6, rounding to the year returns the date at the beginning of the year, otherwise it returns the date at the beginning of the next year.

Select round (sysdate) day,round (sysdate,'w') week,round (sysdate,'month') month,round (sysdate,'year') year from dual

Select round (sysdate,'cc') cc,round (sysdate,'q') Q magnetic round (sysdate,'hh'), round (sysdate,'mi') min hour from dual

Date TRUNC

The date TRUNC function intercepts the value according to the specified date precision format.

The date TRUNC function takes a mandatory parameter and an optional parameter. Syntax: TRUNC (source date [, date precision format]) The source date parameter represents any date item. The date precision format parameter specifies the precision of the intercept, which is optional, and if not specified, the default intercept precision is day. That is, all time parts of the source date are set to midnight (00:00:00). Interception at the month level sets the date of the source date to the first day of the month. The interception on the grade returns the date at the beginning of the year.

Select trunc (sysdate) day,trunc (sysdate,'w') week,trunc (sysdate,'month') month,trunc (sysdate,'year') year from dual

5. Implicit data type conversion

If possible, you can implicitly convert values whose data types do not match the data types of the parameters required by the function to the desired format. VARCHAR2 and CHAR data types are collectively referred to as character types. Character fields are very flexible, allowing almost all types of information to be stored. Therefore, you can easily convert DATE and number values to their character forms. These conversions are called number-to-character (number to character) and date-to-character (date to character) conversions.

Select length (1234567890) from dual

Select length (0123456789) from dual

Select length (sysdate) from dual;.

It is not common to implicitly convert character data to a numeric data type, because the only condition for this is that the character data represents a valid number.

You can convert implicit characters to a date (character to date) when the string conforms to the following date format: [d | DD] separator1 [MON | MONTH] separator2 [R | RR | YY | date]. D and DD represent the first and second digits of the month, respectively. MON is the three-character abbreviation of the month, while MONTH is the full name of the month. R and RR denote satisfactory and 2-digit years, respectively. YY and YYYY represent 2-digit and 4-digit years, respectively. Separator1 and separator2 elements can be most punctuation, spaces, and tabs.

'24murJANMUE 09' DD-MON-RR

'1\ january/8' D\\ MONTH/R

'13The Janus 8' DD*MON*R

'13Universe febmax 2008' DD/MON/YYYY

'01$ jan/08' DD$MON/RR

'24-JAN-09 18pur45' DD-MON-RR HH24:MI

Conversion function TO_CHAR function converts data into 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.

7. Conditional function NVL

The NVL function evaluates whether a column or expression of any data type is null. Returns an alternative non-null value if the original item is null; otherwise, the original item is returned.

The NVL function has two mandatory arguments. Syntax: NVL (original,ifnull). Where original represents the item to be tested, and returns ifnull if the original item evaluates to be empty. The data types of original and ifnull parameters must be the same. They must be of the same type, or it is possible to implicitly convert ifnull to the type of original parameter. The data type of the return value of the NVL function is the same as that of the original parameter.

Select nvl (1234) from dual;-- error

Select nvl (null,1234) from dual;-1234

Select nvl (substr ('abc',4),' No substring exists') from dual

NVL2

The NVL2 function is an enhancement to the NVL function, but the function is very similar. The NVL2 function evaluates whether a column or expression of any data type is null. If the first item is not null, the second parameter is returned, otherwise the third parameter is returned.

The NVL2 function has three mandatory arguments. Syntax: NVL2 (original,ifnotnull,ifnull), where original represents the item being tested. If original is not null, return ifnotnull;. If original is null, return ifnull. The data types of the ifnotnull and ifnull parameters must be the same or the ifnull parameter can be converted to the data type of the ifnotnull parameter, which cannot be the LONG data type. They can be of the same type, or you can convert ifnull to the type of ifnotnull parameter. The data type returned by the NVL2 function is the same as that of the ifnotnull parameter.

Select nvl2 (1234 from dual; 1 million string') from dual;-error

Select nvl2 (null,1234,5678) from dual;-5678

Select nvl2 (substr ('abc',2),' Not bc','No substring') from dual

NULLIF

The NULLIF function tests the equality of the two items. If they are equal, the function returns a null value, otherwise the first item of the two test items is returned.

The NULLIF function has two mandatory parameters that can be of any data type, the two parameter types must be the same, and the first parameter cannot be empty. Syntax: NULLIF (ifunequal,comparison_term), where the parameters ifunequal and comparison_term are compared. If they are the same, return NULL. If they are different, the ifunequal parameter is returned.

Select nullif (1234 people 1234) from dual

Select nullif ('24murJULMULMI 2009') from dual

Select nullif (1 minute null) from dual;-- 1

Select nullif (null,null) from dual;---- returns ORA-00932: inconsistent datatypes: expected-got CHAR

COALESCE

The COALESCE function returns the first non-null value from the argument list. If all parameters are empty, a null value is returned.

The COALESCE function has two mandatory arguments and any number of optional arguments. Syntax: COALESCE (expr1,expr2,...,exprn), return expr1 if it is not null, otherwise, return expr2 if it is not null, and so on. The COALESCE function is a general form of NVL function:

COALESCE (expr1,expr2) = NVL (expr1,expr2)

COALESCE (expr1,expr2) = NVL (expr1,NVL (expr2,expr3))

If a non-null value is found, COALESCE returns the same data type as the first non-null parameter. To avoid a "ORA-00931:inconsistent date types" error, the data type of all non-null parameters must be the same as the data type of the first non-null parameter.

Select coalesce (null,null,null,'a string') from dual

Select coalesce (null,null,null) from dual

Select coalesce (substr ('abc',4),' not bc','no substring') from dual

Select coalesce (substr ('abc',4),' not bc',123) from dual;-- error

DECODE

The DECODE function implements if-then-else conditional logic by testing the equality of the first two terms, returning the third argument if they are equal, and possibly another item if they are not equal.

The DECODE function uses at least three mandatory parameters, but can use more parameters. Syntax: DECODE (expr1,comp1,iftrue1 [, comp2,iftrue2... [, compN,iftrueN]] [, iffalse]). These parameters are calculated as the following pseudo-code example:

If expr1 = comp1 then return iftrue1

Else if expr1 = comp2 then return iftrue 2

...

...

Else if expr1 = compN then return iftrueN

Else return null | iffalse

All arguments to the DECODE function can be expressions. The returned data type is the same as the first data type that matches the comparison option. The expression expr1 is implicitly converted to the data type of the first comparison parameter comp1. When calculating the other comparison parameter comp2...compn, they are also implicitly converted to the same data type as comp1. DECODE assumes that the two null values are equal, so if expr1 is null and comp3 is the first null comparison parameter that occurs, the corresponding result parameter iftrue3 is returned.

Select decode (1234 is a match') from dual

Select decode (1234 is a match','No match') from dual

Select decode ('search','comp1','true1','comp2','true2','search','true3',substr (' 2searchcraftsmagery 2, 6), 'true4','false') from dual

Select decode (null,'comp1','true1','comp2','true2',null,'true3','false') from dual

Select decode (1234 and 123 match') from dual;--'No match'ORA-01722: invalid number

CASE expression

CASE expressions can be implemented in all third-and fourth-generation programming languages. Like the DECODE function, CASE expressions use if-then-else conditional logic. There are two variants of CASE expressions. A simple CASE expression lists conditional search terms once, and each comparison expression tests the equality of the search term. The searched CASE expression lists the individual criteria for each comparison expression.

CASE expressions use at least three mandatory parameters, but you can use more parameters. Its syntax depends on whether you use a simple CASE expression or a search CASE expression.

Syntax for simple CASE expressions:

CASE search_expr

WHEN comparison_expr1 THEN iftrue1

[WHEN comparison_expr2 THEN iftrue2

...

WHEN comparison_exprN THEN iftureN]

[ELSE iffalse]

END

A simple CASE expression is contained in a CASE...END code block and consists of at least one WHEN...THEN statement. In the simplest case-- there is only one WHEN...THEN statement, and search_expr is compared to comparison_expr1. If they are equal, grandma returns the result iftrue1. If it is not equal, a null value is returned, unless the ELSE component is defined, in which case the default iffure value is returned. When there are multiple WHEN...THEN statements in an CASE expression, the matching comparison expression is searched until a match is found.

The search, comparison, and result parameters can be column values, expressions, or all literals, but they must all be of the same data type.

Select

Case substr (1234, 1 and 3)

When '134' then '1234 is a match'

When '1235' then '1235 is a match'

When concat ('1pm is 23') then concat (' 1m record 23') | |'is a match''

Else'no match'

End

From dual

Syntax of the searched CASE expression:

CASE

WHEN condition1 THEN iftrue1

[WHEN condition2 THEN iftrue2

...

WHEN conditionN THEN iftrueN]

[ELSE iffalse]

END

The CASE expression for the search is contained in the CASE...END code block and consists of at least one WHEN...THEN statement. In the simplest case-- there is only one WHEN...THEN statement, calculate condition1;. If it is true, then the result iftrue1 is returned. If not, a null value is returned unless the ELSE component is defined, in which case the default iffase value is returned. When there are multiple WHEN...THEN statements in an CASE expression, the matching comparison expression is searched until a match is found.

Select

Case

When length (substr (1234 length of substring is 1)) = 1 then'1'

When length (substr (1234 and 1)) = 2 then 'length of substring is 2'

When length (substr (1234 length of substring is 1)) = 3 then 'length of substring is 3'

Else'no match'

End

From dual

These are all the contents of the article "what are the common functions of 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.