Oracle database SQL statements, built-in functions Daquan

1. Numerical functions

function return value sample display

Ceil (n) the smallest integer greater than or equal to the value N, SELECT ceil (10.6) from TABLE_NAME; 11

Floor (n) the largest integer less than or equal to the value N, SELECT floor (10.6) from TABLE_NAME; 10

MOD (M,n) m divided by the remainder of N, and if n=0, returns M SELECT MOD (7,5) from table_name l; 2

Power (M,n) m's N-order SELECT POWER (3,2) from table_name; 9

ROUND (n,m) rounds N, retains the M-bit SELECT ROUND (1234.5678,2) from table_name after the decimal point; 1234.57

Sign (n) if n=0, returns 0, otherwise, N>0 returns 1,N<0, 1 SELECT sign (a) from table_name; 1

SQRT (n) N of the square root SELECT SQRT (+) from table_name; 5

2. Characters commonly used character function

Initicap (char) converts the first character of each string into uppercase SELECT initicap (' MR.ECOP ') from table_name; Mr.ecop

LOWER (char) converts the entire string to lowercase SELECT LOWER (' MR.ECOP ') from table_name; Mr.ecop

Replace (CHAR,STR1,STR2) string with all str1 replaced by str2 SELECT REPLACE (' Scott ', ' s ', ' Boy ') from table_name; Boycott

SUBSTR (Char,m,n) takes out a substring of n characters starting with the M character SELECT SUBSTR (' ABCDEF ', 2,2) from table_name; Cd

Length (char) for string lengths SELECT length (' ACD ') from table_name; 3

|| collocated operator SELECT ' ABCD ' | | '  EFGH ' from table_name; Abcdefgh

　　3. Date type function

Sysdate Current date and time SELECT sysdate from table_name;

Last_day last day of the month SELECT Last_day (sysdate) from table_name;

Add_months (d,n) Current date d pushes n months SELECT add_months (sysdate,2) f from table_name;

Months_between (D,n)

Date D and N months of difference SELECT Months_between (sysdate,to_date (' 20020812 ', ' YYYYMMDD ')) Fromtable_name;

Next_day (d,day) d The date of day specified in the first week SELECT Next_day (sysdate, ' Monday ') from table_name;

Day format has ' Monday ' Monday ' Tuesday ' Tuesday

' Wednesday ' Wednesday ' Thursday ' Thursday ' Friday ' Friday

' Saturday ' Saturday ' Sunday ' Sunday

　4. Date type function in special format

Y or yy or YYY the last of the year, two-bit, three-bit Selectto_char (sysdate, ' YYY ') from table_name;

Q quarter, January-March for the first quarter SELECT to_char (sysdate, ' Q ') from table_name;

MM number of months SELECT to_char (sysdate, ' MM ') from table_name;

The RM month of Rome represents SELECT To_char (sysdate, ' RM ') from table_name; IV

Month named by month in 9 characters SELECT To_char (sysdate, ' month ') from table_name;

WW The first few weeks of the year SELECT To_char (sysdate, ' WW ') from table_name l;

W this month the first few weeks SELECT to_char (sysdate, ' W ') from table_name;

DDD Day of the year, January 1 is 001, February 1 032 SELECT to_char (sysdate, ' DDD ') from table_name;

DD the day of the month SELECT To_char (sysdate, ' DD ') from table_name;

The first days of the D week SELECT to_char (sysdate, ' D ') from table_name; such as Sunday

DY Week the first day abbreviation SELECT to_char (sysdate, ' DY ') from table_name; such as Sun

Hh12 12-hour number of hours SELECT to_char (sysdate, ' Hh12 ') from table_name;

Hh24 24-hour number of hours SELECT to_char (sysdate, ' hh24 ') from table_name;

Mi minutes SELECT To_char (sysdate, ' Mi ') from table_name;

SS Seconds of SELECT To_char (sysdate, ' SS ') from TABLE_NAME;

SELECT to_char (sysdate, ' Yyyy-mm-dd hh:24:mi:ss ') from table_name;

To_number () converts a valid string into a digital SELECT to_number (' 88877 ') from table_name; 88877

To_char () Converts a number to a string SELECT to_char (88877) from table_name; ' 88877 '

　　5. 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".

Example SELECT CONCAT (CONCAT (column_name, ' was a '), job) from TABLE_NAME;

2. Initcap (String)

Converts the character "string" to uppercase.

SELECT Initcap (column_name) from table_name;

3. LOWER (String)

Turn "string" to lowercase.

SELECT LOWER (column_name) from table_name;

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".

SELECT Lpad (column_name,15, ' * ') from table_name;

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.

SELECT LTRIM (' Abcdab ', ' a ') from table_name;

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.

SELECT REPLACE (' JACK and Jue ', ' J ', ' BL ') from table_name;

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".

SELECT rpad (column_name,15, ' * ') from table_name;

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.

SELECT RTRIM (' abcdef ', ' f ') from table_name;

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.

SELECT column_name from table_name WHERE SOUNDEX (column_name) = SOUNDEX (' Smythe ');

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",

Start to intercept the tail of "string" from "Start".

SELECT SUBSTR (' abcdefgijklm ', 3,4) from table_name;

One. TRANSLATE (String, if, then)

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.

SELECT TRANSLATE (column_name, ' aeiou ', ' XXXXX ') from table_name;

UPPER (String)

Returns a "string" in uppercase.

SELECT UPPER (' Aptech computer Education ') from TABLE_NAME;

ASCII (String)

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".

SELECT ASCII (' APTECH ') from table_name;

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.

SELECT INSTR (' Aptech is Aptech ', ' ap ', up) from table_name;

. LENGTH (String)

Returns the length value of "string".

SELECT column_name, LENGTH (column_name) from table_name;

　　6. Null function

1.COALESCE (expr)

Returns the first non-null function in the expression list, and returns a null if both are NULL

SELECT COALESCE (null,9-9,null) from table_name;

2.LNNVL (condition)

Returns data other than the conditions required by the condition, including null conditions, which are typically used in the Where condition

SELECT column_name1,... COLUMN_NAME2 from table_name WHERE LNNVL (condition);

3.NVL (EXPR1,EXPR2[,EXPR3])

NVL (EXPR1,EXPR2), replacing the null value, if EXPR1 is a null value, returns the value of EXPR2, otherwise returns the value of Expr1. The function requires that the two parameter types be consistent at least mutually

An implicit conversion is possible between

SELECT NVL (column_name1,column_name2) from table_name;

NVL (EXPR1,EXPR2,EXPR3), which replaces the null value, returns the value of EXPR2 if EXPR1 is a null value, otherwise returns the value of EXPR3. This function requires EXPR2, EXPR3 parameters

Type consistency can be implicitly converted to at least one another

SELECT NVL (COLUMN_NAME1,COLUMN_NAME2,COLUMN_NAME3) from table_name;

　　7. Aggregate function

1.AVG ([distinct l all] expr)

Computes the average of a column, representing the average of a group, returning a numeric type

Distinctlall: Removing duplicate values

All: represents all values, including duplicates, and is also the default value

Expr: An expression that can only be a numeric type

SELECT AVG (all column_name) from table_name [WHERE CONDITION];

2.MAX ([distinct l all] expr) MIN ([distinct l all] expr)

Specify the maximum and minimum values for a column

SELECT * FROM table_name WHERE COLUMN_NAME = (SELECT MAX (column_name) from table_name);

SELECT * FROM table_name WHERE COLUMN_NAME = (SELECT MIN (column_name) from table_name);

3.SUM ([distinct l all] expr) MIN ([distinct l all] expr)

Sum function that returns the value of the specified column and

SELECT SUM (all column_name) from table_name [WHERE CONDITION];

4.COUNT ([distinct l all] expr) MIN ([distinct l all] expr)

Returns the number of records for a query

SELECT COUNT (all column_name) from table_name [WHERE CONDITION];

　　8. Other functions

1.USER

Returns the login function

SELECT USER from DUAL;

2.USERENV (parameter)

Returns the current session information

Parameter:language returns the current session corresponding language and character set

Parameter:sessionid returns the current session ID

PARAMETER:ISDBA returns whether the current user is DBA

SELECT USERENV (' ISDBA ') from table_name [WHERE CONDITION];

3.sys_context (Namespace,parameter)

Returns the value of the attribute named Userenv that Oracle has created for the context

4.DECODE (Expr,search,result[,search1,result1][,default])

The expression matching function, which returns the value of result when expr meets the criteria of search, can repeat multiple, if no match results are put back to default, which is a one-to-one matching process,

Similar to switch

Oracle database SQL statements, built-in functions Daquan