Oracle Development Manual Utility function Encyclopedia

Single-record functions in SQL
1.ASCII
Returns the decimal number corresponding to the specified character;
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) ZHAO,CHR () chr65 from dual;

En C
-- -
Zhao A

3.CONCAT
Connect two strings;
Sql> Select Concat (' 010-', ' 88888888 ') | | Turn 23 ' Gao Hu bid from dual;

Gao Hu Phone
----------------
010-88888888 ext. 23

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

UPP
-----
Smith

5.INSTR (C1,C2,I,J)
Searches for a specified character in a string, returning the location of the specified character;
C1 the string being searched
C2 the string you want to search for
I the start of the search, the default is 1
The position of 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 Name,length (name), Addr,length (addr), Sal,length (To_char (SAL)) from GAO.NCHAR_TST;

Name Length (name) ADDR Length (ADDR) SAL Length (To_char (SAL))
------ ------------ ---------------- ------------ --------- --------------------
Gao Hu 3 Beijing Sea ingot Area 6 9999.99 7

7.LOWER
Returns a string and converts all characters to 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 (paste characters)
Rpad paste characters to the right of the column
Lpad paste characters to the left of the column
Sql> Select Lpad (Rpad (' Gao ', ten, ' * '), "*") from dual;

Lpad (Rpad (' GAO '), 1
-----------------
gao*******
Not enough characters to fill with *

10.LTRIM and RTrim
LTRIM deletes the string appearing on the left
RTRIM Delete the string that appears on the right
Sql> Select LTrim (RTrim (' Gao Qian Jing ', '), ') from dual;

LTRIM (RTRIM ('
-------------
Gao Qian Jing

11.SUBSTR (String,start,count)
Take the substring, starting at start, count
Sql> Select substr (' 13088888888 ', 3,8) from dual;

SUBSTR ('
--------
08888888

12.REPLACE (' string ', ' s1 ', ' s2 ')
String the character or variable you want to be replaced
S1 the replaced string
S2 the string to replace
sql> Select replace (' He love you ', ' he ', ' I ') from dual;

REPLACE (' H
----------
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 (' s ' from ' string ')
Leading cut off the front characters
Trailing cut off the back characters
If not specified, default is spaces

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

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 string anyway
Sql> Select ASIN (0.5) from dual;

ASIN (0.5)
---------
.52359878

18.ATAN
Returns the 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) from dual;

Ceil (3.1415927)
---------------
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 digital inverse cosine value
Sql> select cosh from dual;

COSH (20)
---------
242582598

22.EXP
Returns the n-th square root of a number E
Sql> Select exp (2), exp (1) from dual;

EXP (2) exp (1)
--------- ---------
7.3890561 2.7182818

23.FLOOR
Take an integer for a given number
Sql> Select Floor (2345.67) from dual;

FLOOR (2345.67)
--------------
2345

24.LN
Returns the numeric value of a number
sql> Select ln (1), ln (2), ln (2.7182818) from dual;

ln (1) ln (2) ln (2.7182818)
--------- --------- -------------
0.69314718.99999999

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 a N1 divided by the remainder of the 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 the N1
Sql> Select Power (2,10), Power (3,3) from dual;

Power (2,10) power (3,3)
----------- ----------
1024 27

28.ROUND and Trunc
Rounding according to 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 symbol of number n, greater than 0 to return 1, less than 0 to return-1, equal to 0 to return 0
Sql> Select sign (123), sign ( -100), sign (0) from dual;

SIGN (123) SIGN ( -100) SIGN (0)
--------- ---------- ---------
1-1 0

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

SIN (1.57079)
------------
1

31.SIGH
Returns the value of a 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 (a) from dual;

SQRT (SQRT) (10)
--------- ---------
8 3.1622777

33.TAN
Returns the tangent value of a number
Sql> Select Tan (m), Tan (a) from dual;

Tan Tan (10)
--------- ---------
2.2371609.64836083

34.TANH
Returns the hyperbolic tangent of a number n
Sql> Select Tanh (), tan from dual;

TANH TAN (20)
--------- ---------
1 2.2371609

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

TRUNC1 TRUNC (124.16666,2)
--------- ------------------
100 124.16

36.add_months
Add or Subtract 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 out 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 ')
Gives the date and time of the =other time zone in this time zone
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 ')
Give date date and week x to calculate the next one weeks
Sql> Select Next_day (' 1 August-May-2001 ', ' Friday ') next_day from dual;

Next_day
----------
2 May-May-01

41.SYSDATE
Used to get the current date of 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 requirement given, if fmt= ' mi ' indicates a reservation, truncated 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
Convert character data type to ROWID type
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)
Converts the 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-formed string to hexadecimal

46.ROWIDTOCHAR
Converting a 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 ')
Converts a string to a date in Oracle

49.to_multi_byte
Converts a single-byte character in a string to a multibyte character
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 '));

52.CONVERT (' x ', ' desc ', ' source ')
Converts the X field or the source of a variable to desc
sql> Select Sid,serial#,username,decode (Command,
2 0, ' none ',
3 2, ' Insert ',
4 3,
5 ' SELECT ',
6 6, ' Update ',
7 7, ' delete ',
8 8, ' drop ',
9 ' other '] cmd from v$session where type!= ' background ';

SID serial# USERNAME CMD
--------- --------- ------------------------------ ------
1 1 None
2 1 None
3 1 None
4 1 None
5 1 None
6 1 None
7 1275 None
8 1275 None
9 GAO Select
GAO None

53.DUMP (S,fmt,start,length)
The DUMP function returns the value of a VARCHAR2 type in the internal number format specified by FMT
Sql> Col global_name for A30
Sql> Col dump_string for A50
Sql> Set Lin 200
Sql> Select Global_name,dump (global_name,1017,8,5) dump_string from Global_name;

Global_name dump_string
------------------------------ --------------------------------------------------
ORACLE. World Typ=1 len=12 Characterset=zhs16gbk:w,o,r,l,d

54.empty_blob () and Empty_clob ()
Both functions are functions that are used to initialize large data type fields

55.GREATEST
Returns the maximum value in a set of expressions that compares the encoded size of a 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 returns true if it is
Sql> Select Userenv (' Isdba ') from dual;

Useren
------
FALSE
Sql> Select Userenv (' Isdba ') from dual;

Useren
------
TRUE
Session
Return session Flag
Sql> Select Userenv (' SessionID ') from dual;

USERENV (' SESSIONID ')
--------------------
152
ENTRYID
Return to session population flag
Sql> Select Userenv (' EntryID ') from dual;

USERENV (' ENTRYID ')
------------------
0
INSTANCE
Returns the flag of 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 user's terminal or machine logo
Sql> Select Userenv (' Terminal ') from dual;

USERENV (' Termina
----------------
GAO
Vsize (X)
Returns the size of x (bytes)
Sql> Select Vsize (user), user from dual;

Vsize (user) User
----------- ------------------------------
6 SYSTEM

60.AVG (distinct| All)
All means an average of all values, and distinct only averages for 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 means the maximum value for all values, distinct represents the maximum value for different values, the same only take 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 take once
sql> Select min (All sal) from Gao.table3;

MIN (Allsal)
-----------
1111.11

63.STDDEV (Distinct|all)
For standard deviation, all indicates a standard deviation for all values, and distinct indicates that only different values are asked for standard deviation
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)
To find covariance

Sql> Select Variance (sal) from Scott.emp;

Variance (SAL)
-------------
1398313.9

65.GROUP by
Used primarily to count a group 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
Add restrictive conditions to group statistics
Sql> Select Deptno,count (*), sum (SAL) from Scott.emp GROUP by DEPTNO has count (*) >=5;

DEPTNO COUNT (*) SUM (SAL)
--------- --------- ---------
20 5 10875
30 6 9400
Sql> Select Deptno,count (*), sum (SAL) from Scott.emp has the have count (*) >=5 GROUP by Deptno;

DEPTNO COUNT (*) SUM (SAL)
--------- --------- ---------
20 5 10875
30 6 9400

67.ORDER by
Used to sort out the results of a query
Sql> Select Deptno,ename,sal from scott.emp to Deptno,sal desc;

DEPTNO ename SAL