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 high-dry telephone 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,10,*), 17,*) 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
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-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
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
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,19-3 month -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 requirements given, if Fmt=mi represents a reservation, truncates the 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
Current 1/2 page
12 Next read the full text
