1.ASCII(x):返回与字符x对应的ASCII码;
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(x):给出整数,返回对应的字符;
SQL> select chr(51941),chr(80) from dual;
CHR(51941) CHR(80)
---------- -------
叔 P
3.CONCAT(x,y):连接两个字符串;
SQL> select concat('吉林省-长春市-','新城大街2888号')||'-吉林农业大学' 吉林农大地址 from dual;
吉林农大地址
-----------------------------------------
吉林省-长春市-新城大街2888号-吉林农业大学
4.INITCAP(x):返回字符串并将字符串的每个单词的首字母大写;
SQL> select initcap('a beautiful girl') from dual;
INITCAP('ABEAUTIFULGIRL')
-------------------------
A Beautiful Girl
5.INSTR(x,f,s,o):在x中查找f,返回f所在的位置;
x:被搜索的字符串
f:希望搜索的字符串
s:搜索的开始位置,默认为1
o:出现的位置,默认为1
SQL> select instr('oracle DBA is my current dream','DBA',1,1) dream from dual;
DREAM
----------
8
6.LENGTH(x):返回字符串的长度;
SQL> select length('i am a oracle lover') from dual;
LENGTH('IAMAORACLELOVER')
-------------------------
19
7.LOWER(x):返回字符串,并将所有的字符小写;
SQL> select lower('Oracle LOVER') from dual;
LOWER('ORACLELOVER')
--------------------
oracle lover
8.UPPER(x):返回字符串,并将所有的字符大写;
SQL> select upper('a oracle ACE') from dual;
UPPER('AORACLEACE')
-------------------
A ORACLE ACE
9.RPAD(x,w,p):在字符串x的右边反复添加字符串p,使x的长度达到w;
LPAD(x,w,p):在字符串x的左边反复添加字符串p,使x的长度达到w;
SQL> select rpad('hello',20,'.'),lpad('word',19,'*'),rpad(lpad('你好',8,'@'),33,'##') from dual;
RPAD('HELLO',20,'.') LPAD('WORD',19,'*') RPAD(LPAD('你好',8,'@'),33,'##
-------------------- ------------------- ---------------------------------
hello............... ***************word @@@@你好#########################
10.LTRIM(x,t):在字符串x的左边截去字符串t,如果没有指定t,那么默认截去空格;
RTRIM(x,t):在字符串x的右边截去字符串t,如果没有指定t,那么默认截去空格;
SQL> select ltrim('000000hello','0'), rtrim('word ') from dual;
LTRIM('000000HELLO','0') RTRIM('WORD')
------------------------ -------------
hello word
TRIM(t from x):将字符串x左右的字符串t截去掉;
TRIM(x):将字符串x左右的空格去掉;
SQL> select trim('0'from'0000hello000000') from dual;
TRIM('0'FROM'0000HELLO000000')
------------------------------
hello
SQL> select trim(' dafd0 ') from dual;
TRIM('DAFD0')
-------------
dafd0
11.SUBSTR(x,s,c):截取子字符串,从s开始(包括s),取c个
SQL> select substr('a moonlight ......',3,9) from dual;
SUBSTR('AMOONLIGHT......',3,9)
------------------------------
moonlight
12.REPLACE(x,s1,s2):用字符串s2代替字符串中字符串s1;
x:希望被替换的字符或变量
s1:被替换的字符串
s2:要替换的字符串
SQL> select replace('eygle is a ACE','eygle is','i will be') from dual;
REPLACE('EYGLEISAACE','EYGLEIS
------------------------------
i will be a ACE
13.SOUNDEX(x):获得包含x发音的一个字符串;
create table t(
name varchar2(10)
);
insert into t(name) values('blue');
insert into t(name) values('bloo');
insert into t(name) values('blu');
insert into t(name) values('white');
SQL> select name from t where soundex(name)=soundex('blue');
NAME
----------
blue
bloo
blu
14.NVL(x,v):如果x为空,返回value;否则返回x;
NVL2(x,v1,v2):如果x为空,返回v1,如果x不为空,返回v2;
create table user_info(
user_id varchar2(20) primary key,
user_name varchar2(10),
user_addr varchar2(50)
);
insert into user_info(user_id,user_name)
values('20014','smith');
insert into user_info(user_id,user_name,user_addr)
values('20015','scott','beijing');
insert into user_info(user_id,user_addr)
values('20016','shanghai');
SQL> select nvl(user_name,'Unknow'),nvl(user_addr,'Unknow') from user_info;
NVL(USER_NAME,'UNKNOW') NVL(USER_ADDR,'UNKNOW')
----------------------- --------------------------------------------------
smith Unknow
scott beijing
Unknow shanghai
SQL> select user_id,nvl2(user_addr,'Know','Unknow') addr from user_info;
USER_ID ADDR
-------------------- ------
20014 Unknow
20015 Know
20016 Know