Shulou(Shulou.com)06/01 Report--

Preface

Learning oracle for a period of time, found that there are many functions in oracle, for programmers in the background, a lot of time to learn a lot of other new things, and then remember these functions are not very realistic, so summed up some commonly used functions and examples in oracle, one is to share with you, the other is to forget to consult at any time in the future work. Don't talk too much nonsense, let's go straight to the function.

one。 One-line function

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

1. Character function

(1) concat (str1,str2) string concatenation function

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

(2) initcap (str) capitalizes the first letter of each word and lowercase the other letters

Select initcap ('hello worldview') From dual;-the returned result is' Hello Worldworkers selected initcap ('HELLO world') From dual;-the returned result is' Hello Worldwide'

(3) instr (XFindFind string [, start] [, occurrence]) returns the position of the specified string in a string. You can specify the starting position of the search and return the results of the first search.

-when searching, the subscript is calculated from 1 to select instr ('Hello Worldlings') from dual;-- starts the search at position 1 and returns the position of the first occurrence of o. The result is 5select instr ('Hello Worldword, starting at position 6) from dual;-- starts searching at position 6, and returns the position of the first occurrence of o, and the result is 8select instr (' Hello Worldling, starting at position 1, 2) from dual. -- search from position 1, return the location of the second occurrence of o, and the result is 8

(4) length (str) returns the number of characters in the expression

Select length ('Hello Worldwide') From dual;-- returns result as 12select length ('Zhang San') from dual;-- returns result as 2

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

Select lower ('Hello Worldwide') From dual

(6) lengthb (str) returns the number of bytes in the expression

Select lengthb ('Hello Worldwide') From dual;-- returns a result of 12select lengthb ('Zhang San') from dual;-- returns a result of 6

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

Select upper ('Hello Worldwide') From dual

(8) lpad (str,width [, pad_string]) when the length of the string is not long enough, the left filling is completed. You can specify what character to use to complete the completion, and if not, fill it with spaces.

Select lpad ('Hello Worldweights 20) from dual;-- returns the result as' Hello Worldworkers select lpad ('Hello Worldlings') from dual;-- returns the result as'* Hello Worlds.'

(9) rpad (str,width [, pad_string]) when the string is not long enough, the right filling is completed, and the principle is the same as the left filling.

Select rpad ('Hello Worldweights 20) from dual;-- returns the result as' Hello Worldlings' select rpad ('Hello Worldlings') from dual;-- returns the result as' Hello Worldwaters'

(10) ltrim (x [, trim_string]) removes all specified strings from the left side of the string. If no removed string is specified, the left blank character is removed by default.

Select ltrim ('Hello World!') The result returned by from dual;-- is' Hello World! 'select ltrim (' * + * Hello Worldwide for example)) from dual;-- returns the result for 'Hello Worldwide'.

(11) rtrim (x [, trim_string]) removes all specified strings from the right side of the string, using the same principle as ltrim ()

Select rtrim ('Hello World!') The result returned by from dual;-- is' Hello Worldworkers select rtrim ('* * + * Hello Worldwaters selected rtrim ('* * + * Hello Worldlings') from dual;-- returns the result as'* + * Hello Worldlings'

(12) trim (trim_string from x) removes all specified strings from both sides of the string. Note that the intercept set of ltrim () and rtrim () can make multiple characters, but the intercept set of trim can only have one character.

Select trim ('* + 'from' *) from dual

(13) nvl converts one NULL to another, and returns value if x is NULL, otherwise the x value itself is returned.

Insert into student values (7 'Pig', default,NULL); select nvl (address,' Beijing') from student

(14) nvl2 (x _ value _ 1m _ value2), return value1 if x is not NULL, otherwise return value2

Select nvl2 (address,' has address','no address') from student

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

Select replace ('Hello Worldman') from dual

(16) substr (xfocus start [, length]) returns the specified characters in the string, starting from the start position of the string and having a length of length characters; if start is negative, from the end of the x string; if length is omitted, all characters up to the end of the string will be returned

Select substr ('Hello World',3) from dual;-- returns result as' llo World'select substr ('Hello World',-3) from dual;-- returns result as' rld'select substr ('Hello World',3,2) from dual;-- returns result as' ll'select substr ('Hello World',-7,4) from dual;-- returns result as' o Wo'

two。 Numerical function

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

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;-return result is 3

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

Select floor (2.3) from dual;-return result is 2

(4) trunc (value,n) truncates value and retains n-decimal places if n > 0; n1000-error

3. After grouping, you need to use criteria for filtering, then use having to filter the grouped rows, and you cannot use where,where to put them in front of the group by.

Select deptno, avg (sal) from emp where deptno10 group by deptno having avg (sal) > 900

Summary

The above is the whole content of this article, I hope that the content of this article has a certain reference and learning value for your study or work, if you have any questions, you can leave a message and exchange, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.