Oracle Common system functions

2 character Functions

1. Replace (string 1, String 2, String 3)

Replace (char, search_string, replace_string)

Function: Search for "String 2" in "String 1" and replace it with "string 3".

For example, the following command replaces the "A" that appears in all employee names with "China".

Sql>selectreplace (ename, ' A ', ' China ') from Scott.emp;

2. InStr (C1, C2, I, J)

Function: Searches for the specified character in a string and returns the location of the specified character. which

C1 string to be searched

C2 the string you want to search

I Search Start location, default is 1

J first appears, default is 1

For example, the following command is to find out where the second Ra "oracletraning" appears.

SQL>SELECTINSTR (' Oracle traing ', ' ra ', ' n ') from dual;

3. ASCII (single character)

Function: Returns the decimal number corresponding to the specified character.

Sql>selectascii (' A ') A, ASCII (' a ') A, ASCII (') space from dual;

Description: Dual is a special table provided internally by the Oracle system for temporary data calculations with only one column of dummy.

4. CHR (integer)

Function: Gives an integer that returns the corresponding character.

SQL>SELECTCHR (54740) Zhao, Chr (+) char65 from dual;

5. CONCAT (String 1, String 2)

Function: Connect two strings.

Selectconcat (' 0532-', ' 96656 ') | | ' Dial 0 ' Laoshan spa set water fromdual;

Selectconcat (ename, ' excellent employee ') from Scott.emp;

The Function and | | The role is the same.

6. Initcap (String)

Function: Returns a string and capitalizes the first letter of the string.

Selectinitcap (' Smith ') Upp from dual;

Selectinitcap (ename) ename from Scott.emp;

7. LENGTH (String)

Function: Returns the length of a string

For example: Query employee name, name character length, salary and salary number length.

Selectename, Length (ename), Sal, Length (To_char (SAL)) from Scott.emp;

For example: Please query the name of the employee with a character length of 4

select* from scott.emp where length (ename) = 4;

Selectlength (' Li Ming ') from dual; --Length of 2, non-English-Chinese, all accounted for 1 characters

Description

The length Functionsreturn the length of char. Length calculates length usingcharacters as defined by the input character Set.

-Returns the length in characters.

LENGTHB usesbytes instead of characters.

-Returns the length in bytes.

LENGTHC Usesunicode complete characters.

-Returns the length in Unicode full characters.

LENGTH2 usesUCS2 code points.

--Returns the length in UCS2 code point units.

LENGTH4 usesUCS4 code points.

--Returns the length in UCS4 code point units.

The following example compares the differences in the calculation functions of different lengths:

CreateTable S (a char (5), B nchar (5), C varchar (5), D nvarchar2 (5));

Insertinto S values (' AA ', ' AA ', ' AA ', ' AA ');

Insertinto S values (' Hello ', ' hello ', ' hello ', ' hello ');

Insertinto S values (' Hello! ', ' Hello! ', ' Hello! ', ' Hello! ');

Selectlength (a), a, length (b), Length (c), Length (d) from S;

SELECTLENGTHB (a), A,LENGTHB (b), LENGTHB (c), LENGTHB (d) from S;

SELECTLENGTHC (a), a,lengthc (b), LENGTHC (c), LENGTHC (d) from S;

8. LOWER (String)

Function: Returns a string, and all characters are lowercase.

Selectlower (' ABBBCCDD ') ABBBCCDD from dual;

9. UPPER (String)

Function: Returns a string and capitalizes all characters.

Selectupper (' ABBBCCDD ') ABBBCCDD from dual;

SUBSTR (String,start, Count)

Function: Takes a substring, starting with start and taking count.

Selectsubstr (' 13370840627 ', 3,5) from dual;

For example: please lower the initials of the employee and capitalize the other letters.

Selectlower (substr (ename,1,1)) | | Upper (Substr (Ename,2,length (ename)-1)) from Scott.emp;

Rpad and Lpad functions

Function: Paste character on right/left of column

For example: Display Page1 to occupy 15 characters, insufficient portion left/right with *. placeholder.

Selectlpad (' Page 1 ', 15, ' *. ') "Lpad example" from dual;

Selectrpad (' Page 1 ', 15, ' *. ') "Rpad example" from dual;

LTrim and RTrim.

Function: Delete the left/right occurrences of the string

Examples are as follows:

Selectltrim (' Qingdao University ', ' Q ') from dual;

SOUNDEX.

Function: Returns a string with the same pronunciation as the given string

Create table table1 (XM varchar (8));

Insert intotable1 values (' weather ');

Insert intotable1 values (' wether ');

Insert intotable1 values (' Goose ')

Select xmfrom table1 where Soundex (XM) =soundex (' weather ');

TRIM (' s ' from ' string ')

Function: Removes certain characters before and after the specified string.

For example:

Selecttrim (0 from 0098123400) "TRIM example" from dual;

To_char (datetime,string format)

Function: Converts a date type to a string.

To_char (Number,stringformat)

Function: Converts a numeric value to a string

For example:

Selectto_char (sysdate, ' Yyyy-mm-dd hh24:mi:ss ') from dual;

Selectename,to_char (Sal, ' l99g999d99 ') from Scott.emp;

Description

9: Displays the number and ignores the previous 0

0: Displays the number, if the number of digits is insufficient, then uses 0 to be padded

.: Displays the decimal point at the specified location

,: Displays a comma at the specified location

$: Before the number plus USD

L: Add a local currency symbol before the number

C: Add the International currency symbol before the number

G: Display the group separator at the specified position

D: Display decimal symbol at specified position

To_number (String)

Function: Converts the given string to a number.

For example:

Selectto_number (' 1999 ') year from dual;

Decode functions

Function: Equivalent to an IF statement

Example:

Create Tablestudent (Sno char (2), sex char (1), birthday date)--Creating student tables

Insert intostudent values (' n ', ' M ', ' 1 August-August-1992 '); --Add record

Insert intostudent values (' n ', ' T ', ' September-May-1993 '); --Add record

Insert intostudent values (' n ', ' F ', ' 1 August-January-1994 '); --Add record

Insert intostudent values (' n ', null, ' November-August-1993 '); --Add record

Select *from Student; --Query Student form

/* Query The student's number and gender information, if the gender value of M is displayed male, the gender value of f is displayed female, if the null value is displayed unknow, otherwise it is displayed invalid*/

Selectsno,sex,decode (Sex, ' M ', ' Male ', ' F ', ' female ', null, ' Unknow ', ' invalid ') fromstudent;

For example: Query the student table to count the number of students born in 1992, 1993 and 1994 years.

Selectto_char (trunc (Birthday, ' year '), ' yyyy '), COUNT (*)

From student

Whereto_char (trunc (Birthday, ' year '), ' yyyy ') in (' 1994 ', ' 1993 ', ' 1992 ')

Group Byto_char (trunc (Birthday, ' year '), ' yyyy ')

The decode function often plays a very flexible role in SQL troubleshooting. One of them is to convert the rows of a table into columns for some purpose. For example:

Selectsum (Decode (To_char (trunc (Birthday, ' year '), ' yyyy '), ' 1994 ', 1,0) birth_1994,

SUM (Decode (To_char (trunc (Birthday, ' year '), ' yyyy '), ' 1993 ', 1,0) birth_1993,

SUM (Decode (To_char (trunc (Birthday, ' year '), ' yyyy '), ' 1992 ', 1,0) birth_1992

From student

Whereto_char (trunc (Birthday, ' year '), ' yyyy ') in (' 1994 ', ' 1993 ', ' 1992 ')

Greatest functions

Function: Returns the maximum value in a set of expressions, that is, the encoding size of the comparison character.

Example:

Selectgreatest (' AD ', ' AC ', ' AA ') from dual;

least functions

Function: Returns the minimum value in a set of expressions, that is, the encoding size of the comparison character.

Example:

Selectleast (' AD ', ' AC ', ' AA ') from dual;

. uid function

Function: Returns a unique integer that identifies the current user

Example:

Show user

Select Uidfrom Dual;

selectusername,user_id from Dba_users where User_id=uid;

2 Numeric Functions

1. ABS

Function: Take absolute value

Selectabs (+), ABS ( -100) from dual;

2. ACOS

Function: Gives the value of the inverse cosine

Selectacos ( -1) from dual;

3. ASIN

Function: Give the value of the inverse chord

Selectasin (0.5) from dual;

4. ATAN

Function: Returns the inverse tangent value of a number

Selectatan (1) from dual

5. Ceil

Function: Returns the smallest integer greater than or equal to the given number

Selectceil (3.1415926) from dual;

6. COS

Function: Returns the cosine of a given number

Selectcos ( -3.14) from dual;

7. EXP

Function: Returns the n-order of a number E

Selectexp (2), exp (1) from dual;

8. Floor

Function: Take an integer for the given number (the decimal digit is dropped)

Selectfloor (234.56) from dual;

9. LN

Function: Returns the pair value of a number

SELECTLN (1), ln (2) from dual;

Ten. Log (N1,N2)

Function: Returns the logarithm of N2 with N1 base

Selectlog (2,1), log (2,4) from dual;

MOD (N1,N2)

Function: Returns the remainder of a N1 divided by N2

Selectmod (10,3) mod (2,3) from dual;

POWER (N1,N2)

Function: Returns the N2 of N1

Selectpower (2,5) from dual;

ROUND.

Function: Rounded (rounded) by the specified precision

TRUNC.

Function: Rounded by the specified precision (for interception, no interception to the number of the first, the default takes an integer).

Example:

Selectround (55.5), trunc (55.5), round ( -55.5), Trunc ( -55.5) from dual;

Selecttrunc (12.345,2), Trunk (12.234,-2) from dual;

Selectto_char (Trunc (sysdate, ' hh '), ' yyyy.mm.dd hh24:mi:ss ') hh, To_char (trunc (sysdate, ' mi '), ' yyyy.mm.dd hh24:mi:ss ' ) hhmm from dual;

Selecttrunc (sysdate) from dual--2014-3-18today's date is2014-3-18
Select Trunc (sysdate, ' mm ') from dual--2014-3-1returns the first day of the month.
Select Trunc (sysdate, ' yy ') from dual--2014-1-1return the first day of the year
Select Trunc (sysdate, ' DD ') from dual--2014-3-18return to current month day
Select Trunc (sysdate, ' yyyy ') from dual--2014-1-1return the first day of the year
Select Trunc (sysdate, ' d ') from dual--2014-3-16 (Sunday)returns the first day of the current week
Select Trunc (sysdate, ' hh ') from dual--2014-3-1814:00:00Current time is14:41
Select Trunc (sysdate, ' mi ') from dual--2014-3-1814:41:00 TRUNC ()function does not have the exact seconds

Sign

Function: Take the sign of the number n, greater than 0 returns 1, less than 0 returns-1, equals 0 returns 0

Select sign ( -10), sign (0) from dual;

SQRT (N)

Function: Returns the square root of the number n

SELECTSQRT (+), sqrt (ten) from dual;

. AVG (distinct| All)

Function: return average

Selectavg (SAL) from Scott.emp;

Selectavg (SAL) from EMP;

Max (distinct| All)

Function: Returns the maximum value

Selectmax (SAL) from Scott.emp;

Min (distinct| All)

Function: Returns the minimum value

Selectmin (SAL) from Scott.emp;

StdDev (distinct| All)

Function: Seek standard deviation

Selectstddev (SAL) from Scott.emp;

Variance (distinct| All)

Function: to ask for covariance

Selectvariance (SAL) from Scott.emp;

2 Date Function

1. add_months

Function: Add or subtract months

Example:

Selecthiredate, Add_months (hiredate,2) from Scott.emp;

Selectto_char (Add_months (to_date (' 199912 ', ' yyyymm '), 2), ' Yyyymm ') from dual;

For example: Please check the last three months of employees (add hiredate 3 months, if the new date is greater than the current date to meet the query requirements)

Select *from emp where add_months (hiredate,3) >=sysdate

Select *from emp where hiredate>=add_months (sysdate,-3)

2. Last_day

Function: Returns the last day of the current month's date

Selectlast_day (sysdate) from dual;

3. Months_between (Date1, Date2)

Function: Give the month of Date1-date2

Example:

Selectmonths_between (to_date (' 2013.05.20 ', ' yyyy.mm.dd '), to_date (' 2014.04.20 ', ' yyyy.mm.dd ')) MON_BETW from dual;

4. New_time (date, ' This ', ' that ')

Function: gives the date and time of the time date in this ' time zone corresponding to that ' time zone

Example:

Selectto_char (sysdate, ' yyyy.mm.dd hh24:mi:ss ') bj_time, To_char (New_time (sysdate, ' PDT ', ' GMT '), ' yyyy.mm.dd Hh24:mi: SS ') Los_angles from dual;

Description: This,that corresponds to the time zone and its shorthand, Atlantic Standard Time: AST or ADT; Alaska _ Hawaii Time: HST or HDT; UK Daylight Time: BST or BDT; US Mountain times: MST or MDT; US Central Time zone: CST or CDT Newland Standard Time: NST, est or EDT, PST or PDT, Greenwich Mean Time: Gmt;yukou Standard Time: Yst or YDT.

5. Next_day (date, ' Day ')

Function: Date given date and day of week X after one weeks

Selectnext_day (' 1 August-May-2013 ', ' Friday ') Next_dayfrom dual; --What's the next Friday?

6. To_date (String, ' format ')

Function: Converts a string into a date (format) in Oracle

Note: the date column is inserted by default in the Format (day-month-year).

YY: Two-digit year 2004---> 04

YYYY: Four-digit year 2004

MM: Two-digit month August--à08

Dd:2 digit number of days 30th-à30

Hh24:8 Point---à20

Hh12:8 Point----à08

MI, ss----à display minutes \ sec

Example:

Insert into Scott.emp (empno,hiredate)

VALUES (2222,to_date (' 1988-11-11 ', ' yyyy-mm-dd '))