Oracle Common functions and examples

Learning Oracle also for some time, found that the functions of Oracle many, for the background of the program ape, a lot of time to learn many other new things, and then remember these functions are not very realistic, so summed up some of Oracle's common functions and examples, One is to share with you, second, you can forget the work in the future at any time to check. Nonsense not much to say, the following directly on the function.

One-line function

Only a single row is processed, and a result is returned for each row.

1. Character functions

(1) concat (STR1,STR2) string concatenation function

Select concat (' Hello ', ' world ') from dual;--equivalent to select ' Hello ' | | ' World ' from dual;

(2) Initcap (str) capitalize the first letter of each word, with the other letters lowercase

Select Initcap (' Hello world! ') from dual; --Return the result as ' Hello world! ' Select Initcap (' HELLO world! ') from dual; --Return the result as ' Hello world! '

(3) InStr (X,find_string[,start][,occurrence]) returns the position of the specified string in a string, specifying the starting position of the search and returning the results of the first search

----------Search the current index starts from 1 to calculate the Select InStr (' Hello world! ', ' O ') from dual;--to start with a 1-bit search, returning the position of the first occurrence o, resulting in 5select InStr (' Hello world! ', ' O ', 6) from dual;--to search from the 6 position, return the position of the first occurrence o, the result is 8select InStr (' Hello world! ', ' O ', ") from dual;--to start the search from 1 position, Returns the position of the second occurrence o, resulting in 8

(4) Length (str) returns the number of characters in an expression

Select Length (' Hello world! ') from dual;--returns a result of 12select length (' Zhang San ') from dual;--return result to 2

(5) LENGTHB (str) returns the number of bytes in an expression

Select LENGTHB (' Hello world! ') from dual;--returns a result of 12select LENGTHB (' Zhang San ') from dual;--returns a result of 6

(6) lower (str) converts a string to lowercase

Select Lower (' Hello world! ') from dual;

(7) Upper (str) converts a string to uppercase

Select Upper (' Hello world! ') from dual;

(8) Lpad (str,width[,pad_string]) when the string length is not enough, the left padding is complete, you can specify what character to fill when the completion, if not specified, the space is padded

Select Lpad (' Hello world! ', ') from dual;--returns the result '        Hello world! ' Select Lpad (' Hello world! ', ' * ') from dual;--returns the result ' ********hello world! '

(9) Rpad (str,width[,pad_string]) when the string length is not enough, the right padding, the principle with the left padding

Select Rpad (' Hello world! ', ') from dual;--returns the result ' Hello world!        ' Select Rpad (' Hello world! ', ' *+ ') from dual;--returns the result as ' Hello world!*+*+*+*+ '

LTrim (x[,trim_string]) removes all specified strings from the left of the string and, if no stripped string is specified, removes the left margin by default

Select LTrim ('   hello world!    ') from dual;--returns a result of ' Hello world!    ' Select LTrim (' ***+*hello world!***+* ', ' *+ ') from dual;--returns the result as ' Hello world!***+* '

(one) RTrim (x[,trim_string]) removes all specified strings from the right side of the string, with the principle LTrim ()

Select RTrim ('   hello world!    ') from dual;--returns a result of '    Hello world! ' Select RTrim (' ***+*hello world!***+* ', ' *+ ') from dual;--returns a result of ' ***+*hello world! '

Trim (trim_string from x) removes all specified strings from both sides of the string

Select Trim (' *+ ' from ' ***+*hello world!***+* ') from dual;

Note that the Intercept set of LTrim () and RTrim () can make multiple characters, but the clipping set of trim must have only one character

Select Trim (' *+ ' from ' ***+*hello world!***+* ') from dual;--error, intercept set can have only one character

NVL (X,value) converts one null to another value, or value if x is null, otherwise the X value itself is returned

INSERT into student values (7, ' Piggy ', default,null), select NVL (Address, ' Beijing ') from student;

NVL2 (x,value1,value2), if x is not NULL, returns VALUE1, otherwise, returns value2

Select NVL2 (Address, ' addresses ', ' no address ') from student;

Replace (x,search_string,replace_string), search for the search_string string from the string x, and replace it with the replace_string string. Does not modify the original values in the database

Select Replace (' Hello world! ', ' o ', ' HA ') from dual;

(+) substr (X,start[,length]) returns the specified characters in a string, starting at the start of the string, with length long, or starting at the end of the X string if start is a negative number; if length is omitted , all characters will be returned to the end of the string

Select substr (' Hello World ', 3) from dual; --return result for ' Llo world ' select substr (' Hello World ', -3) from dual;--return result for ' rld ' select substr (' Hello World ', 3,2) from dual;-- return result for ' ll ' select substr (' Hello world ', -7,4) from dual;--return result for ' O Wo '

2. Numeric functions

(1) ABS (value) returns the absolute value of

Select ABS ( -10) from dual;--Returns a result of 10

(2) Ceil (value) returns the smallest integer greater than or equal to value

Select Ceil (2.3) from dual; --Returns a result of 3

(3) Floor (value) returns the largest integer less than or equal to value

Select Floor (2.3) from dual; --Returns a result of 2

(4) trunc (value,n) Truncate value, if n>0, retain n decimal, n<0, then leave the-n bit integer digit, n=0, then subtract fractional part

Select Trunc (555.666) from dual; --return result is 555, default minus n when the decimal part select Trunc (555.666,2) from dual;--return result is 555.66select trunc (555.666,-2) from dual;--return result is 500

(5) Round (value,n) rounds value to save n bits to the right of the decimal point. If n is omitted, it is equivalent to the case of n=0

Select Round (555.666) from dual;--Returns a result of 556, minus n when the decimal portion of select round (555.666,2) from dual;--is returned with a result of 555.67select round ( 555.666,-2) from dual;--Returns a result of 600

Note: 1.trunc and round usage are similar, except that trunc is hard to intercept and not rounded, while round is rounded when intercepted
2. Can also be the date of interception, you can refer to the written Date function note

Select round (Sysdate, ' year ') from Dual;select trunc (Sysdate, "year") from dual;

3. Conversion functions

Convert a value from one type to another, or from one format to another

(1) To_char (X[,format]): Converts x to a string. Format is converted, can be in number format or date format

Select To_char (' 12345.67 ') from dual; --The return result is 12345.67select to_char (' 12345.67 ', ' 99,999.99 ') from dual; --return result is 12,345.67

(2) To_number (x [, format]): Converts x to a number. You can specify the format

Select To_number (' 970.13 ') + 25.5 from Dual;select to_number ('-$12,345.67 ', ' $99,999.99 ') from dual;

(3) cast (x as type): Converts x to the specified compatible database type

Select CAST (12345.67 as VARCHAR2), cast (' May-July -07 ' as date), cast (12345.678 as Number (10,2)) from dual;

(4) to_date (x [, format]): Converts the X string to a date

Select To_date (' 2012-3-15 ', ' YYYY-MM-DD ') from dual

Two. Aggregation function

1. Common functions

(1) AVG (x): Returns the average of X

Select AVG (grade) from SC;

(2) count (x): Number of rows to return statistics

Select count (name) from SC;

(3) Max (x): Returns the maximum value of X

Select Max (grade) from SC;

(4) min (x): Returns the minimum value of x

Select min (grade) from SC;

(5) sum (x): Returns the total value of X

Select sum (grade) from SC;

2. Using aggregate functions for grouped rows

Using the aggregate function for the grouped rows, the aggregation function counts the values in each group and returns a value for each group, respectively.

Example

--according to the position group, the highest and lowest wage for each position select job, Max (Sal), Min (sal) from the EMP group by job order by job;

Note: 1. The column name behind the SELECT clause must be the same as the column name after the GROUP BY clause, unless it is an aggregate function

Select Deptno,avg (SAL) from emp;--error because Deptno is not a clustered function and is not a column name followed by a group by

2. You cannot use the aggregate function as a filter condition for the WHERE clause

Select Deptno from emp where AVG (SAL) >1000;--error

3. After grouping, you need to use conditions to filter, then use having to filter grouped rows, cannot use Where,where can only be placed in front of group by.

Select Deptno, Avg (SAL) from EMP where deptno<>10 GROUP by DEPTNO have  avg (SAL) > 900;

Oracle Common functions and examples