Functions commonly used by Oracle

1, commonly used functions are divided into five major categories:

character functions, numbers and date functions, numeric functions, conversion functions, mixed functions

2. Character functions

Character functions are primarily used to modify character columns. These functions accept character input and return a character or numeric value. Some of the character functions provided by Oracle are as follows.

1. CONCAT (CHAR1, CHAR2)

Returns the "Char1" of the Connection "CHAR2".

2. Initcap (String)

Converts the character "string" to uppercase. This can only convert the first letter to uppercase, with caution

3. LOWER (String)

Turn "string" to lowercase. This is all the characters are converted to lowercase, and not the same as the top

4. Lpad (Char1,n [, Char2])

Returns "Char1", and the characters in "char2" are added to the "n" character length from the left. If "char1" is longer than "n", then the function returns the first "n" Characters of "char1". Example

5. LTRIM (String,trim_set)

Delete the character from the left, where "string" is the database column, or literal string, and "Trim_set" is the set of characters we want to remove. Example

6. REPLACE (String, if, then)

Replaces characters in a string with 0 or other characters. "If" is a character or string, and for each "if" appearing in "string", the contents of "then" are replaced. Example

7. Rpad (CHAR1, n [, Char2])

Returns "Char1" with the characters in "Char2" on the right side added to the "n" character. If "char1" is longer than "n", then the function returns the first "n" Characters of "char1". Example

8. RTRIM (String,trim_set)

Remove the character from the right, where "string" is the database column, or literal string, and "Trim_set" is the set of characters we want to remove. Example

9. SOUNDEX (char)

Returns a string that contains the ideographic character of "char". It allows you to compare words that are pronounced in English with different spellings. Example

SUBSTR (String, start [, Count])

Returns part of the Intercept in "string". This command intercepts a subset of "string", starting at the "start" position and continuing with "count" characters. If we do not specify "count", The end of "string" is truncated from "start". Example

TRANSLATE (String, if, then) this and replace do not know what the difference is, this limit point position, replace all

The position of the character in the "if" and check the same position of "then", and then replace the character in "string" with the character in that position. Example

UPPER (String) This is all capitalized.

Returns a "string" in uppercase. Example

ASCII (String) writes no more, and returns only the first one.

This command is abbreviated as "American standard Code for information interchange". It is the basic rule that uses numbers to represent printable characters. The function returns the ASCII value of the first (leftmost) character in "string". Example

INSTR (String, set[, start[, occurrence])

The command "string" in the "start" position to find the location of the character set, and then find "set" appears the first, second, and so on "occurrence" (number of times). The value of "start" can also be a negative number, representing the search in the opposite direction starting at the end of the string. The function is also used for numeric and date data types. Example

. LENGTH (String)

Returns the length value of "string". Example

2. Data and date functions

Oracle allows great flexibility in the use of dates. Because the time and date can be stored in a date field, there are functions that can reference both the date and the reference time. Some of the date functions provided by Oracle are as follows.

1. Sysdate

Returns the current date and time. Example

2. Add_months (d, No_of_month)

Returns the date "D" with "No_of_month". The parameter "No_of_month" can be any integer. Example

3. Last_day (Month_day)

Returns the date of the last day of the month specified in the variable "Month_day". Example

4. Months_between (D1, D2)

Returns the number of months between the date D1 and D2. If D1 is later than D2, the result is positive, otherwise a negative number is returned. Example

5. Next_day (d, Day_of_week)

Returns the date of the first business day after the date specified by the variable "D", named after "Day_of_week". The parameter "Day_of_week" must be one of the day of the week. Example

3, the numeric function numeric function accepts the numeric input, returns the numeric value as the output. The value returned by a numeric function can be exactly 38 decimal digits. Some of the number functions provided by Oracle are as follows.

1. ABS (n)

Returns the absolute value of N. Example

2. (n)

Returns the smallest integer greater than or equal to N. Example

3. Floor (N)

Returns the largest integer less than or equal to N. Example

4. MOD (M,n)

Returns the remainder after M is removed by N. If n is 0, however, the M is returned. Example

5. POWER (M, N)

Returns the nth exponentiation of M. Base m and exponent n can be any number, but if M is negative, n must be an integer. Example

6. ROUND (value, precision)

Round rounds the number given in "value" to the decimal place specified in "precision". Example

7. SQRT (N)

Returns the square root of N. The n value cannot be negative. Example

8. TRUNC (N[,m])

Returns the truncated rounding to the N of the M-decimal place. Example

4. Conversion function

In addition to character, numeric, and date functions, Oracle also provides conversion functions. A conversion function converts a value from one data type to another data type. Some of the conversion functions provided by Oracle are as follows.

1.to_char (d [, FMT])

Converts the "D" of the date data type to a value of the VARCHAR2 data type, as specified by the date format "FMT". If "FMT" is omitted, D is converted to the VARCHAR2 value in the default date format. Example

2.to_char (n [, FMT])

Converts the "n" of the number data type to a value of the VARCHAR2 data type using the optional numeric format "FMT". If "FMT" is omitted, N is converted to a VARCHAR2 value that is exactly the number of digits of its significant number. Example

3.to_number (char [, FMT])

Converts a char or VARCHAR2 data type containing a number "char" to the format specified by the format "FMT", which is the numeric data type. Example

4.to_data (CHAR,FMT)

Converts a char or VARCHAR2 value to a value of a date type in a specified format. Example

5. Mixed function

1.GREATEST (expr [, expr] ...)

Returns the maximum value of a list of values. For character data, the ASCII value is compared. Example

2.LEAST (expr [, expr] ...)

Returns the minimum value of a list of values. For character data, the ASCII value is compared. Example

3.NVL (value Substitute)

If the value is null, the value is replaced. Example

4.USER

Returns the current Oracle user with the VARCHAR2 data type. Example

5.VSIZE (expr)

Returns the number of bytes of the inner expression of expr, which returns null if expr is null. Example

6.

AD or A.D.
AD tags with or without a period

BC or B.C.
BC marks with or without a period

D
Day of the Week (1-7)

Day
Name of the day (Sunday-saturday)

Dd
The day of January (1-31)

Ddd
Day of the Year (1-366)

Dy
The abbreviation of the Day (Sun-sat)

HH
Hours of the day (1-12)

HH24
Hours of the day (0-23)

MI
Minutes (0-59)

Mm
Month (01-12)

MON
Abbreviation for month name

MONTH
Name of the Month

Ss
Seconds (0-59)

YYYY
The Year 4 numbers represent

Functions commonly used by Oracle