Oracle Development Manual Utility function Encyclopedia

Source: Internet
Author: User
Tags abs acos asin character set chr numeric value sin square root

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.