Oracle Common database built-in 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 difference months SELECT months_between (sysdate,to_date (' 20020812 ', ' YYYYMMDD ') from table_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 () will be a valid numeric string SELECT to_number (' 88877 ') from table_name; 88877

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

　

SET Serveroupt on;

Dbms_output. Put_Line (' HelloWorld ')

　

SET Headingoff does not require a table header because it is creating a data file

SET PAGESIZE0 does not require paging

Set LINESIZE80 maximum size of the row

SET Echooff tells Sqlplus not to echo statements when executing statements

SET Feedbackoff suppresses sqlplus to show how many rows that satisfy a query are retrieved

COL Salesformat 999,999,999

APPEND adding text to the end of the current line

change/old/new/replaces old text with new text in current line

Change/text Delete Wenb from the current row

DEL Delete When moving forward

INPUT TEXT adds a row after the current line

List displays all rows in the buffer

List n shows the nth row in the buffer

LIST mn display m to n

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 ') fromtable_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 wheresoundex (column_name) = SoUNDEX (' Smythe ');

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

SELECT SUBSTR (' abcdefgijklm ', 3,4) fromtable_name;

11.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;

12.UPPER (String)

Returns a "string" in uppercase.

SELECT UPPER (' Aptech computer Education ') Fromtable_name;

13.ASCII (String)

This command is an abbreviation for "American Standard Code forinformation 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;

14.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) fromtable_name;

15.LENGTH (String)

Returns the length value of "string".

SELECT column_name, LENGTH (column_name) from table_name;

Oracle Common database built-in functions