关于字符函数的一些应用总结
字符函数接受字符参数,一般来说可以用于任意表达式,字符函数以某种方式处理,给用户返回结果。下面总结了一些常用的字符函数:
SQL> select * from v$version where rownum<2;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
1.ASCII() 和 CHR()
ASCII(x)用于获得字符x的ascii码,CHR()用于获得ascii码为x的字符:==>为互逆过程
SQL> select ascii('a'),ascii('A'),ascii('z'),ascii('Z'),ascii(0) from dual;
ASCII('A') ASCII('A') ASCII('Z') ASCII('Z') ASCII(0)
---------- ---------- ---------- ---------- ----------
97 65 122 90 48
SQL> select CHR(97),CHR(65),CHR(122),CHR(90),CHR(48) from dual;
CH CH CH CH CH
-- -- -- -- --
a A z Z 0
2.CONCAT()
CONCAT(x,y)函数用于将y添加到x之后,该函数返回得到的是字符串:
SQL> select concat(id,name) from tt where rownum=1 ORDER BY ID;
CONCAT(ID,NAME)
--------------------------------------------------------------------------------
6hong
这个函数和连字符||功能相同:
SQL> select id||name from tt where rownum=1 order by id;
ID||NAME
--------------------------------------------------------------------------------
6hong
3.INITCAP()
用于将x中的每个单词的字母首字母转换成大写:
SQL> select initcap(id)||' '||initcap(name) from tt where rownum=1 order by id;
INITCAP(ID)||''||INITCAP(NAME)
--------------------------------------------------------------------------------
instr(x,fing_string,[,start][,occurrence])用于在x中查找find_string,并返回find_string所在的位置,其中start是可选参数,表明x是从哪个位置开始查找,还可以用可选参数occurrence说明返回find_string第几次出现的位置:
<1>不带参数
SQL> select name,instr(name,'I') from diy;
NAME INSTR(NAME,'I')
---------------------------------------- ---------------
AIAIAIAIAIAIAIAI 2
<2>从开头第二次出现字母I开始:
SQL> select name,instr(name,'I',1,2) from diy;
NAME INSTR(NAME,'I',1,2)
---------------------------------------- -------------------
AIAIAIAIAIAIAIAI 4
5.LENGTH()
用于获取length(x)函数中字符x的个数:
SQL> select length(name) from diy;
LENGTH(NAME)
------------
16
6.LOWER()和UPPER()
lower(x)函数用于将x中的字母转换成小写,upper(x)函数将x中的字母转换成大写:
SQL> select upper(name),lower(name) from diy;
UPPER(NAME) LOWER(NAME)
---------------------------------------- ---------------------------------------
-
AIAIAIAIAIAIAIAI aiaiaiaiaiaiaiai
7.LPAD()和RPAD()
lpad(x,width[,pad_string])函数用于在x的左边补齐空格,使x的总长度达到width个字符。如果在pad_string参数中指定了一个字符串,那么就使用这个字符重复的填充x左边的空位,以补齐x的长度,补齐后字符串作为结果返回,同理,rpad(x,width[,pad_string])函数用于在x的右边补齐字符串:
SQL> select rpad(name,10,'.'),lpad(name,10,'.') from wang where rownum=1;
RPAD(NAME,10,'.')
LPAD(NAME,10,'.')
--------------------------------------------------------------------------------
-------------------------------------------------------------------------------
-
WANG......
......WANG
8.LTRIM()和RTRIM()和TRIM()
ltrim(x[,trim_string])函数用于从x的左边截取一些字符,该函数还可以用可选的参数trim_string来指定要截去的字符,如果没有指定trim_string参数,默认情况下截去的是空格。同理,rtrim(x[,trim_string])函数用于从x的右边截取一些字符,trim(x[,trim_string])函数用于从x的左右边截取一些字符。
SQL> select rtrim('diy os! ') from dual;
RTRIM('DIYOS!')
----------------
diy os!
SQL> select rtrim('diy os!00000000','0') from dual;
RTRIM('DIYOS!000
----------------
diy os!
SQL> select trim('0'from'00000000diy os00000000') from dual;
TRIM('0'FROM'0
--------------
diy os
9.NVL()
nvl(x,value)用于将空值转换成一个已知的值,如果x为空,返回value,否则返回x:
SQL> select * from wang;
NAME ID
------------ ----------
2
WANG 1
SQL> select id,nvl(name,'the name is null') from wang;
ID NVL(NAME,'THENAMEISNULL')
---------- --------------------------------
2 the name is null
1 WANG
10.NVL2()
nvl2(x,value1,value2)中,如果x为非空,返回value1,否则返回value2:
SQL> select * from wang;
NAME ID
------------ ----------
2
WANG 1
SQL> select id,nvl2(name,'the name is not null','the name is null') from wang;
ID NVL2(NAME,'THENAMEISNOTNULL','THENAMEISN
---------- ----------------------------------------
2 the name is null
1 the name is not null
11.REPLACE()
repSQL> select replace(name,'WANG','diy') from wang where id=1; ==>注意这里的search_string,大小写一定要和表里的一致
lace(x,search_string,replace_string)用于在x中查找search_string,并将其替换成replace_string:
REPLACE(NAME,'WANG','DIY')
------------------------------------
diy
12.SOUNDEX()
soundex(x)用于获得包含x发音的一个字符串,该函数用于对英文拼写不同但发音相识的单词进行比较。
SQL> select name from wang where soundex(name) = soundex(
2 'whyte');
NAME
------------
white
是不是很有意思!
13.SUBSTR()
SUBSTR(x,start[,length])用于从x中取得的从start位置开始的一个字符串,还可以使用可选参数length指定字符串的长度:
<1>用于表中的列:
SQL> select * from diy;
NAME
----------------------------------------
AIAIAIAIAIAIAIAI
SQL> select substr(name,15) from diy;
SUBSTR(NAME,15)
------------------------------------------------
AI
SQL> select substr(name,15,2) from diy;
SUBSTR(NAME,15,2
----------------
AI
<2>用于任意表达式:
SQL> select substr('wangdiywaNBdiy',10,2) from diy;
SUBS
----
NB
<3>也可用于函数的任意组合:
SQL> select lower(substr('wangdiywaNBdiy',10,2)) from diy;
LOWE
----
nb
SQL> select * from v$version where rownum<2;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
1.ASCII() 和 CHR()
ASCII(x)用于获得字符x的ascii码,CHR()用于获得ascii码为x的字符:==>为互逆过程
SQL> select ascii('a'),ascii('A'),ascii('z'),ascii('Z'),ascii(0) from dual;
ASCII('A') ASCII('A') ASCII('Z') ASCII('Z') ASCII(0)
---------- ---------- ---------- ---------- ----------
97 65 122 90 48
SQL> select CHR(97),CHR(65),CHR(122),CHR(90),CHR(48) from dual;
CH CH CH CH CH
-- -- -- -- --
a A z Z 0
2.CONCAT()
CONCAT(x,y)函数用于将y添加到x之后,该函数返回得到的是字符串:
SQL> select concat(id,name) from tt where rownum=1 ORDER BY ID;
CONCAT(ID,NAME)
--------------------------------------------------------------------------------
6hong
这个函数和连字符||功能相同:
SQL> select id||name from tt where rownum=1 order by id;
ID||NAME
--------------------------------------------------------------------------------
6hong
3.INITCAP()
用于将x中的每个单词的字母首字母转换成大写:
SQL> select initcap(id)||' '||initcap(name) from tt where rownum=1 order by id;
INITCAP(ID)||''||INITCAP(NAME)
--------------------------------------------------------------------------------
6 Hong
4.INSTR()instr(x,fing_string,[,start][,occurrence])用于在x中查找find_string,并返回find_string所在的位置,其中start是可选参数,表明x是从哪个位置开始查找,还可以用可选参数occurrence说明返回find_string第几次出现的位置:
<1>不带参数
SQL> select name,instr(name,'I') from diy;
NAME INSTR(NAME,'I')
---------------------------------------- ---------------
AIAIAIAIAIAIAIAI 2
<2>从开头第二次出现字母I开始:
SQL> select name,instr(name,'I',1,2) from diy;
NAME INSTR(NAME,'I',1,2)
---------------------------------------- -------------------
AIAIAIAIAIAIAIAI 4
5.LENGTH()
用于获取length(x)函数中字符x的个数:
SQL> select length(name) from diy;
LENGTH(NAME)
------------
16
6.LOWER()和UPPER()
lower(x)函数用于将x中的字母转换成小写,upper(x)函数将x中的字母转换成大写:
SQL> select upper(name),lower(name) from diy;
UPPER(NAME) LOWER(NAME)
---------------------------------------- ---------------------------------------
-
AIAIAIAIAIAIAIAI aiaiaiaiaiaiaiai
lpad(x,width[,pad_string])函数用于在x的左边补齐空格,使x的总长度达到width个字符。如果在pad_string参数中指定了一个字符串,那么就使用这个字符重复的填充x左边的空位,以补齐x的长度,补齐后字符串作为结果返回,同理,rpad(x,width[,pad_string])函数用于在x的右边补齐字符串:
SQL> select rpad(name,10,'.'),lpad(name,10,'.') from wang where rownum=1;
RPAD(NAME,10,'.')
LPAD(NAME,10,'.')
--------------------------------------------------------------------------------
-------------------------------------------------------------------------------
-
WANG......
......WANG
8.LTRIM()和RTRIM()和TRIM()
ltrim(x[,trim_string])函数用于从x的左边截取一些字符,该函数还可以用可选的参数trim_string来指定要截去的字符,如果没有指定trim_string参数,默认情况下截去的是空格。同理,rtrim(x[,trim_string])函数用于从x的右边截取一些字符,trim(x[,trim_string])函数用于从x的左右边截取一些字符。
SQL> select rtrim('diy os! ') from dual;
RTRIM('DIYOS!')
----------------
diy os!
SQL> select rtrim('diy os!00000000','0') from dual;
RTRIM('DIYOS!000
----------------
diy os!
SQL> select trim('0'from'00000000diy os00000000') from dual;
TRIM('0'FROM'0
--------------
diy os
9.NVL()
nvl(x,value)用于将空值转换成一个已知的值,如果x为空,返回value,否则返回x:
SQL> select * from wang;
NAME ID
------------ ----------
2
WANG 1
SQL> select id,nvl(name,'the name is null') from wang;
ID NVL(NAME,'THENAMEISNULL')
---------- --------------------------------
2 the name is null
1 WANG
10.NVL2()
nvl2(x,value1,value2)中,如果x为非空,返回value1,否则返回value2:
SQL> select * from wang;
NAME ID
------------ ----------
2
WANG 1
SQL> select id,nvl2(name,'the name is not null','the name is null') from wang;
ID NVL2(NAME,'THENAMEISNOTNULL','THENAMEISN
---------- ----------------------------------------
2 the name is null
1 the name is not null
repSQL> select replace(name,'WANG','diy') from wang where id=1; ==>注意这里的search_string,大小写一定要和表里的一致
lace(x,search_string,replace_string)用于在x中查找search_string,并将其替换成replace_string:
REPLACE(NAME,'WANG','DIY')
------------------------------------
diy
soundex(x)用于获得包含x发音的一个字符串,该函数用于对英文拼写不同但发音相识的单词进行比较。
SQL> select name from wang where soundex(name) = soundex(
2 'whyte');
NAME
------------
white
是不是很有意思!
13.SUBSTR()
SUBSTR(x,start[,length])用于从x中取得的从start位置开始的一个字符串,还可以使用可选参数length指定字符串的长度:
<1>用于表中的列:
SQL> select * from diy;
NAME
----------------------------------------
AIAIAIAIAIAIAIAI
SQL> select substr(name,15) from diy;
SUBSTR(NAME,15)
------------------------------------------------
AI
SQL> select substr(name,15,2) from diy;
SUBSTR(NAME,15,2
----------------
AI
SQL> select substr('wangdiywaNBdiy',10,2) from diy;
SUBS
----
NB
SQL> select lower(substr('wangdiywaNBdiy',10,2)) from diy;
LOWE
----
nb
Stay Hungry. Stay Foolish.