Summary of common ORACLE functions (80) page 1/2

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 ZERO SPACE
------------------------------------
65 97 48 32

2. CHR
Returns the corresponding characters;
SQL> select chr (54740) zhao, chr (65) chr65 from dual;
ZH C
---
Zhao
3. CONCAT
Connect two strings;
SQL> select concat (010-, 88888888) | forwarded to 23 Gao Qian's competing phone number from dual;
Gao Qian's phone number
----------------
010-88888888 to 23
4. INITCAP
Returns a string and converts the first letter of the string to uppercase;
SQL> select initcap (smith) upp from dual;
UPP
-----
Smith

5. INSTR (C1, C2, I, J)
Searches for a specified character in a string and returns the location where the specified character is found;
String searched by C1
String to be searched by C2
The start position of the I search. The default value is 1.
Where J appears. The default value 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 Qian Jing 3 Beijing haiding District 6 9999.99 7

7. LOWER
Returns a string and lowercase letters of all characters.
SQL> select lower (AaBbCcDd) AaBbCcDd from dual;
AABBCCDD
--------
Aabbccdd

8. UPPER
Returns a string and upper-case all characters.
SQL> select upper (AaBbCcDd) upper from dual;
UPPER
--------
AABBCCDD

9. RPAD and LPAD (paste characters)
Pad the character on the right of the column
LPAD paste characters on the left of the column
SQL> select lpad (rpad (gao, 10, *), 17, *) from dual;
LPAD (RPAD (GAO, 1
-----------------
* ******* Gao *******
If the character is not enough, use * to fill it up.

10. LTRIM and RTRIM
LTRIM deletes the string on the left
RTRIM deletes 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)
Substring, starting from start, count
SQL> select substr (13088888888,3, 8) from dual;
SUBSTR (
--------
08888888

12. REPLACE (string, s1, s2)
String: the character or variable to be replaced.
String to be replaced by s1
String to be replaced by s2
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 a 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
TRAILING
If this parameter is not specified, the space character is used by default.
15. ABS
Returns the absolute value of a specified value.
SQL> select abs (100), abs (-100) from dual;
ABS (100) ABS (-100)
------------------
100 100

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

22. EXP
Returns the n root of the number e.
SQL> select exp (2), exp (1) from dual;
EXP (2) EXP (1)
------------------
7.3890561 2.7182818

23. FLOOR
Returns an integer to a given number.
SQL> select floor (2345.67) from dual;
FLOOR (2345.67)
--------------
2345

26. MOD (n1, n2)
Returns the remainder of n1 divided by n2.
SQL> select mod (10, 3), mod (3, 3), mod (2, 3) from dual;
MOD)
---------------------------
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
Round 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
Returns 1 if the number n is greater than 0,-1 if the value is less than 0, and 0 if the value is 0.
SQL> select sign (123), sign (-100), sign (0) from dual;
SIGN (123) SIGN (-100) SIGN (0)
----------------------------
1-1 0

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
----------
September 31-04

38. MONTHS_BETWEEN (date2, date1)
Given the month of the date2-date1
SQL> select months_between (March---1999) mon_between from dual;
MON_BETWEEN
-----------
9
SQL> selectmonths_between (to_date (2000100005.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 in this time zone = other 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)
Returns the date of the week and the date of the next week after week x.
SQL> select next_day ('18-May-2001 ', 'Friday') next_day from dual;
NEXT_DAY
----------
25-5-01

41. SYSDATE
Used to obtain 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 given requirements. If fmt = mi, the minute is retained and the second is truncated.
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 SMITH
AAAAfKAACAAAAEqAAB ALLEN
AAAAfKAACAAAAEqAAC WARD
AAAAfKAACAAAAEqAAD JONES

43. CONVERT (c, dset, sset)
Convert the source string sset from one language character set to another destination dset Character Set
SQL> select convert (strutz, we8hp, f7dec) "conversion" from dual;
Conver
------
Strutz