How to use Oracle SQL built-in functions and their results

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