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 gives an integer and returns the corresponding character;
Sql> Select Chr (54740) ZHAO,CHR () chr65 from dual;
En C
-- -
Zhao A
3.CONCAT connection two strings;
Sql> Select Concat (' 010-', ' 88888888 ') | | Turn 23 ' Gao Hu bid from dual;
Gao Hu Phone
----------------
010-88888888 ext. 23
4.INITCAP returns the 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 a string for the specified character, 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. 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 the string and capitalizes all the 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 (' heloveyou ', ' he ', ' I ')
------------------------------
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 value of the specified values
Sql> Select ABS (M), ABS ( -100) from dual;
ABS (-100)
--------- ---------
100 100
16.ACOS gives the value of the inverse cosine
Sql> Select ACOs ( -1) from dual;
ACOS (-1)
---------
3.1415927
17.ASIN gives the value of the inverse chord
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 number given
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 takes 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 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 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 square 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 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 takes the number n symbol, is greater than 0 returns 1, is 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 (a) from dual;
SQRT (SQRT) (10)
--------- ---------
8 3.1622777
33.TAN returns the tangent 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.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 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 to binary
45.RAWTOHEXT converts a binary-formed string to hexadecimal
46.ROWIDTOCHAR convert ROWID data type to 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 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) specifies an external binary file
Sql>insert into FILE_TB1 values (bfilename (' Lob_dir1 ', ' image1.gif '));
52.CONVERT (' x ', ' desc ', ' source ') converts the origin of the X field or 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 is primarily used 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 has NT (*) >=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 TNO;
DEPTNO COUNT (*) SUM (SAL)
--------- --------- ---------
20 5 10875
30 6 9400
67.ORDER by is used to sort the results of the query to output
Sql> Select Deptno,ename,sal from scott.emp to Deptno,sal desc;
DEPTNO ename SAL
--------- ---------- ---------
Ten KING 5000
Ten CLARK 2450
Ten MILLER 1300
SCOTT 3000
FORD 3000
JONES 2975
ADAMS 1100
SMITH 800
2850 BLAKE
ALLEN 1600
TURNER 1500
WARD 1250
MARTIN 1250
JAMES 950