Oracle Common function Induction

Single-record functions in SQL
1.ASCII
Returns the decimal number corresponding to the specified character (note: is a character rather than a string);
Sql> Select ASCII (' a ') a,ascii (' A ') a,ascii (' 0 ') zero,ascii (') space from dual;
A A ZERO SPACE
--------- ------
65 97 48 32

2.CHR
Give an integer and return the corresponding character;
Sql> Select Chr (54740), Chr (+) from dual;
ZH C
-- -
Zhao A

3.CONCAT
Connect two strings;
Sql> Select Concat (' 010-', ' 88888888 ') | | ' Ext. 23 ' Gao Hu call from dual;
Gao Hu Bid Phone
----------------
010-88888888 ext. 23

4.INITCAP
Returns a string and capitalizes the first letter of the string;
Sql> Select Initcap (' Smith ') Upp from dual;
Smith
-----
Smith

5.INSTR (C1,C2,I,J)--to determine if a character or string exists, there is an index that returns where it appears, otherwise it returns less than 1
Searches for the specified character in a string, returning the position of the specified character;
C1 string to be searched
C2 the string you want to search
I Search start position, default is 1
Where J appears, default is 1
Sql> Select InStr (' Oracle traning ', ' Ra ', 1, 2) instring from dual;
Instring
---------
9

6.LENGTH
Returns the length of the string;
sql> SELECT Length (' Gao Hu race '), Length (' Beijing Sea ingot area '), Length (' 9999.99 ') from dual;
Gao Hu Beijing Hai ingot District 9999.99
------ ------------  ----------------
3 6 7

7.LOWER
Returns a string, and all characters are lowercase
Sql> Select lower (' AABBCCDD ') AABBCCDD from dual;
Aabbccdd
--------
Aabbccdd

8.UPPER
Returns a string and capitalizes all characters
Sql> Select Upper (' AABBCCDD ') upper from dual;
UPPER
--------
Aabbccdd

9.RPAD and Lpad (pasting characters)
Rpad pasting characters to the right of a column
Lpad pasting characters to the left of a column
sql> SELECT lpad (' Lin Yi-chin ', ten, ' * ') from dual;
Lin Yi-chin
-----------------
Lin Yi-chin
Not enough characters to fill with *

10.LTRIM and RTrim
LTRIM Delete empty string that appears to the left
RTRIM Delete the empty string that appears to the right
sql> SELECT ltrim (' Lin Yi-chin ') from dual; --Delete empty strings that appear to the left
Lin Yi-chin
-------------
Lin Yi-chin

11.SUBSTR (String, start, count)
Takes a substring, starting with start, taking count
Sql> Select substr (' 13088888888 ', 3, 8) from dual;
13088888888
--------
08888888

12.REPLACE (' string ', ' s1 ', ' s2 ')
The character or variable that string wants to be replaced
S1 replaced string
S2 the string to replace
sql> Select replace (' She Love you ', ' she ', ' I ') from dual;
She Love You
----------
I love You

13.SOUNDEX
Returns a string with the same pronunciation as the given string
Sql> CREATE TABLE table1 (XM varchar (8));
sql> INSERT INTO table1 values (' weather ');
sql> INSERT INTO table1 values (' wether ');
sql> INSERT INTO table1 values (' Gao ');
Sql> Select XM from table1 where Soundex (XM) =soundex (' weather ');
Xm
--------
Weather
Wether

14.TRIM (' string ')
Remove empty strings on the left and right sides of a string
If not specified, the default is whitespace

15.ABS
Returns the absolute value of the specified values
Sql> Select ABS (+), ABS ( -100) from dual;
ABS (+) ABS (-100)
--------- ---------
100 100

16.ACOS
Give the value of the inverse cosine
Sql> Select ACOs ( -1) from dual;
ACOS (-1)
---------
3.1415927

17.ASIN
Give the value of the inverse chord.
Sql> Select ASIN (0.5) from dual;
ASIN (0.5)
---------
.52359878

18.ATAN
Returns the inverse tangent value of a number
Sql> Select Atan (1) from dual;
ATAN (1)
---------
.78539816

19.CEIL
Returns the smallest integer greater than or equal to the given number
Sql> Select Ceil (3.1415927), Ceil (3.415927), Ceil (3.715927), Ceil (3.9415927) from dual;
3.1415927 3.415927 3.715927 3.9415927
---------- ---------- ---------- ---------
4 4 4 4

20.COS
Returns the cosine of a given number
sql> Select cos ( -3.1415927) from dual;
COS (-3.1415927)
---------------
-1

21.COSH
Returns a numeric inverse cosine value
Sql> select cosh from dual;
COSH (20)
---------
242582598

22.EXP
Returns the nth root of a number E
Sql> Select exp (2), exp (1) from dual;
EXP (2) exp (1)
--------- ---------
7.3890561 2.7182818

23.FLOOR
Takes an integer to a given number
Sql> Select Floor (2345.67) from dual;
Floor (2345.67)
--------------
2345

24.LN
Returns the pair value of a number
sql> Select ln (2) from dual;
LN (2)
---------
0.693147180559945

25.LOG (N1,N2)
Returns the logarithm of a N1 base N2
Sql> Select Log (2,1), log (2,4) from dual;
Log (2,1) log (2,4)
--------- ---------
0 2

26.MOD (N1,N2)
Returns the remainder of a N1 divided by N2
Sql> Select mod (10,3), mod (3,3), mod (2,3) from dual;
MoD (10,3) mod (3,3) mod (2,3)
--------- --------- ---------
1 0 2

27.POWER
Returns the N2 root of N1
Sql> Select Power (2,10), Power (3,3) from dual;
Power (2,10) power (3,3)
----------- ----------
1024 27

28.ROUND and Trunc
Rounding with the specified precision
Sql> Select Round (55.5), round ( -55.4), trunc (55.5), Trunc ( -55.5) from dual;
ROUND (55.5) ROUND ( -55.4) TRUNC (55.5) TRUNC (-55.5)
----------- ------------ ----------- ------------
56-55 55-55

29.SIGN
Take the sign of the number n, greater than 0 returns 1, less than 0 returns-1, equals 0 returns 0
Sql> Select sign (123), sign ( -100), sign (0) from dual;
SIGN (123) sign ( -100) sign (0)
--------- ---------- ---------
1-1 0

30.SIN
Returns the sinusoidal value of a number
sql> Select sin (1.57079) from dual;
SIN (1.57079)
------------
1

31.SIGH
Returns the value of the hyperbolic sine
sql> Select sin (sinh) from dual;
SIN (SINH) (20)
--------- ---------
.91294525 242582598

32.SQRT
Returns the root of the number n
sql> Select sqrt, sqrt (ten) from dual;
SQRT (SQRT) (10)
---------  ---------
8 3.1622777

33.TAN
Returns the tangent value of a number
Sql> Select Tan, Tan (ten) from dual;
Tan (10)
--------- ---------
2.237160 9.64836083

34.TANH
Returns the hyperbolic tangent value of the number n
Sql> Select Tanh, Tan (a) from dual;
TANH (+) TAN (20)
--------- ---------
1 2.2371609

35.TRUNC
Intercepts a number according to the specified precision
Sql> Select Trunc (124.1666,-1), Trunc (176.1666,-2), Trunc (124.16666, 2) from dual;
Trunc (124.1666,-1) trunc (176.1666,-2) trunc (124.16666, 2)
---------             -----------         ---------
120 100 124.16

36.add_months
Add or subtract a month
Sql> Select To_char (add_months (to_date (' 199912 ', ' yyyymm '), 2), ' Yyyymm ') from dual;
To_cha
------
200002
Sql> Select To_char (add_months (to_date (' 199912 ', ' yyyymm '), -2), ' Yyyymm ') from dual;
To_cha
------
199910

37.last_day
Returns the last day of the date
Sql> Select To_char (sysdate, ' yyyy.mm.dd '), To_char ((sysdate) +1, ' Yyyy.mm.dd ') from dual;
To_char (SY to_char (S
---------- ----------
2004.05.09 2004.05.10
Sql> Select Last_day (sysdate) from dual;
Last_day (S
----------
3 January-May-04

38.months_between (DATE2,DATE1)
Give the month of date2-date1
Sql> Select Months_between (' 1 September-December-1999 ', ' 1 September-March -1999 ') Mon_between from dual;
Mon_between
-----------
9
Sql>selectmonths_between (to_date (' 2000.05.20 ', ' yyyy.mm.dd '), to_date (' 2005.05.20 ', ' yyyy.mm.dd ')) MON_BETW from Dual
Mon_betw
---------
-60

39.new_time (date, ' This ', ' that ')
Returns the date and time of the time zone 2 that corresponds to time zone 1.
Sql> Select To_char (sysdate, ' yyyy.mm.dd hh24:mi:ss ') Bj_time,to_char (new_time
2 (sysdate, ' PDT ', ' GMT '), ' yyyy.mm.dd hh24:mi:ss ') los_angles from dual;
Bj_time Los_angles
------------------- -------------------
2004.05.09 11:05:32 2004.05.09 18:05:32

40.next_day (date, ' Day ')
Date given date and week x after one weeks
Sql> Select Next_day (' 1 August-May-2001 ', ' Friday ') next_day from dual;
Next_day
----------
2 May-May-01

41.SYSDATE
The current date used to get the system
Sql> Select To_char (sysdate, "Dd-mm-yyyy Day") from dual;
To_char (Sysdate, '
-----------------
09-05-2004 Sunday
Trunc (DATE,FMT) truncates the date according to the given requirements if the fmt= ' mi ' represents a reserved point, truncating the seconds
Sql> Select To_char (trunc (sysdate, ' hh '), ' yyyy.mm.dd hh24:mi:ss ') hh,
2 To_char (trunc (sysdate, ' mi '), ' yyyy.mm.dd hh24:mi:ss ') hhmm from dual;
HH HHMM
------------------- -------------------
2004.05.09 11:00:00 2004.05.09 11:17:00

42.CHARTOROWID
Converting character data types to ROWID types
Sql> Select rowID, Rowidtochar (ROWID), ename from Scott.emp;
ROWID Rowidtochar (ROWID) ename
------------------ ------------------ ----------
AAAAFKAACAAAAEQAAA aaaafkaacaaaaeqaaa SMITH
Aaaafkaacaaaaeqaab Aaaafkaacaaaaeqaab ALLEN
AAAAFKAACAAAAEQAAC AAAAFKAACAAAAEQAAC WARD
Aaaafkaacaaaaeqaad Aaaafkaacaaaaeqaad JONES

43.CONVERT (C,dset,sset)
Convert source string sset from one language character set to another dset character set
Sql> Select CONVERT (' Strutz ', ' we8hp ', ' f7dec ') ' conversion ' from dual;
Conver
------
Strutz

44.HEXTORAW
Converts a hexadecimal-formed string into a binary

45.RAWTOHEXT
Converts a binary-composed string to hexadecimal

46.ROWIDTOCHAR
Convert the ROWID data type to a character type

47.to_char (date, ' format ')
Sql> Select To_char (sysdate, ' Yyyy/mm/dd hh24:mi:ss ') from dual;
To_char (sysdate, ' YY
-------------------
2004/05/09 21:14:41

48.to_date (String, ' format ')
Convert a string to a date in Oracle

49.to_multi_byte
Convert single-byte characters in a string into multibyte characters
Sql> Select To_multi_byte (' High ') from dual;
To
--
High

50.to_number
Converts the given character to a number
Sql> Select To_number (' 1999 ') year from dual;
Year
---------
1999

51.BFILENAME (Dir,file)
Specify an external binary file
Sql>insert into FILE_TB1 values (bfilename (' Lob_dir1 ', ' image1.gif '));

54.empty_blob () and Empty_clob ()
Both functions are functions used to initialize a field of a large data type

55.GREATEST
Returns the maximum value in a set of expressions, that is, the encoding size of the comparison character.
Sql> Select Greatest (' AA ', ' AB ', ' AC ') from dual;
Gr
--
AC
Sql> Select Greatest (' Ah ', ' ann ', ' Day ') from dual;
Gr
--
Days

56.LEAST
Returns the minimum value in a set of expressions
Sql> Select least (' Ah ', ' ann ', ' Day ') from dual;
LE
--
Ah

57.UID
Returns a unique integer that identifies the current user
Sql> Show User
USER is "GAO"
Sql> Select username,user_id from dba_users where User_id=uid;
USERNAME user_id
------------------------------ ---------
GAO 25

58.USER
Returns the name of the current user
Sql> Select User from dual;
USER
------------------------------
GAO

59.USEREVN
To return information about the current user environment, opt can be:
Entryid,sessionid,terminal,isdba,lable,language,client_info,lang,vsize
ISDBA to see if the current user is a DBA and returns true if yes
Sql> Select Userenv (' Isdba ') from dual;
Useren
------
FALSE
Sql> Select Userenv (' Isdba ') from dual;
Useren
------
TRUE
SESSION
Returns the session flag
Sql> Select Userenv (' SessionID ') from dual;
USERENV (' SESSIONID ')
--------------------
152
ENTRYID
Return session population flag
Sql> Select Userenv (' EntryID ') from dual;
USERENV (' ENTRYID ')
------------------
0
INSTANCE
Returns the flag for the current instance
Sql> Select Userenv (' instance ') from dual;
USERENV (' INSTANCE ')
-------------------
1
LANGUAGE
Returns the current environment variable
Sql> Select Userenv (' language ') from dual;
USERENV (' LANGUAGE ')
----------------------------------------------------
Simplified Chinese_china. Zhs16gbk
Lang
Returns the abbreviation for the language of the current environment
Sql> Select Userenv (' Lang ') from dual;
USERENV (' LANG ')
----------------------------------------------------
ZHS
TERMINAL
Returns the logo of the user's Terminal or machine
Sql> Select Userenv (' Terminal ') from dual;
USERENV (' Termina
----------------
GAO
Vsize (X)
Returns the size (in bytes) of X
Sql> Select Vsize (user), user from dual;
Vsize (user) User
----------- ------------------------------
6 SYSTEM

60.AVG (distinct| All)
All means that all values are averaged, and distinct only averages the different values.
Sqlwks> CREATE TABLE Table3 (XM varchar (8), Sal number (7,2));
Statement has been processed.
sqlwks> INSERT into table3 values (' Gao ', 1111.11);
sqlwks> INSERT into table3 values (' Gao ', 1111.11);
sqlwks> INSERT into table3 values (' Zhu ', 5555.55);
Sqlwks> commit;
Sql> Select AVG (Distinct sal) from Gao.table3;
AVG (Distinctsal)
----------------
3333.33
Sql> Select AVG (all sal) from Gao.table3;
AVG (Allsal)
-----------
2592.59

61.MAX (distinct| All)
The maximum value, all represents the maximum value for all values, distinct represents the maximum value for the different values, the same only once
Sql> Select MAX (distinct sal) from Scott.emp;
MAX (Distinctsal)
----------------
5000

62.MIN (distinct| All)
The minimum value, all means the minimum value for all values, distinct represents the minimum value for different values, the same only once
sql> Select min (All sal) from Gao.table3;
MIN (Allsal)
-----------
1111.11

63.STDDEV (Distinct|all)
Standard deviation, all means a standard deviation for all values, distinct means only the standard deviation for different values
Sql> Select StdDev (sal) from Scott.emp;
STDDEV (SAL)
-----------
1182.5032
Sql> Select StdDev (Distinct sal) from Scott.emp;
STDDEV (Distinctsal)
-------------------
1229.951

64.VARIANCE (distinct| All)
Ask for covariance
Sql> Select Variance (sal) from Scott.emp;
VARIANCE (SAL)
-------------
1398313.9

65.GROUP by
Used primarily to count a set of numbers
Sql> Select Deptno,count (*), sum (SAL) from the Scott.emp group by Deptno;
DEPTNO COUNT (*) SUM (SAL)
--------- --------- ---------
10 3 8750
20 5 10875
30 6 9400

66.HAVING
Additional restrictions on grouping statistics
Sql> Select Deptno,count (*), sum (SAL) from Scott.emp Group by DEPTNO have Count (*) >=5;
DEPTNO COUNT (*) SUM (SAL)
--------- --------- ---------
20 5 10875
30 6 9400
Sql> Select Deptno,count (*), sum (SAL) from Scott.emp have count (*) >=5 GROUP by Deptno;
DEPTNO COUNT (*) SUM (SAL)
--------- --------- ---------
20 5 10875
30 6 9400

67.ORDER by
Used to sort output the results of a query
Sql> Select Deptno,ename,sal from scott.emp order by deptno,sal Desc;
DEPTNO ename SAL
--------- ---------- ---------
Ten KING 5000
CLARK 2450
Ten MILLER 1300
SCOTT 3000
FORD 3000
JONES 2975
ADAMS 1100
800 SMITH
2850 BLAKE
ALLEN 1600
1500 TURNER
WARD 1250
MARTIN 1250
JAMES 950

Oracle Common function Induction