本文详细介绍了 Oracle 数据库中的字符串操作函数，包括大小写转换、子串抽取、查找替换等功能，通过具体实例展示了如何高效地处理字符串数据。
oracle中的单行函数（一）
单行函数：每处理一条记录返回一条结果.
Single-row functions return one row of results for each record processed.
下面介绍下oracle中的大小写转换函数，字符串操作函数。
大小写转换函数：lower,upper,initcap
字符串操作函数：substr,instr,length,lpad/rpad,ltrim/rtrim,replace,translate,concat
lower语法格式：
lower（c）.c是字符串或者是要转换的域。
SQL> select first_name,last_name,commission_pct
2 from employees
3 where lower(last_name)='king';
FIRST_NAME LAST_NAME COMMISSION_PCT
-------------------- ------------------------- --------------
Steven King
Janette King 0.35
lower函数在select语句中使用不会改变数据库中字符的大小写形式。当在插入或者
更新语句中使用lower函数的时候，可能会影响数据库中存储的字符的大小写形式。
注意查询展现的字符的大小写形式还是存储的时候的大小写形式。
如果想让展现的字符都变成大写或者小写可以使用下面的方式：
SQL> select lower(first_name),lower(last_name),commission_pct
2 from employees
3 where lower(last_name)='king';
LOWER(FIRST_NAME) LOWER(LAST_NAME) COMMISSION_PCT
-------------------- ------------------------- --------------
steven king
janette king 0.35
注意上面的表头是带有函数的，可以使用别名来消除表头。
SQL> select lower(first_name) as first_name,lower(last_name) as last_name,commission_pct
2 from employees
3 where lower(last_name)='king';
FIRST_NAME LAST_NAME COMMISSION_PCT
-------------------- ------------------------- --------------
steven king
janette king 0.35
upper函数的语法格式：
upper(c).c表示要转换的字符或者需要转换的域。
SQL> select firstname,lastname,city
2 from customers
3 where lastname=upper(&lastname);
输入 lastname 的值: 'smith'
原值 3: where lastname=upper(&lastname)
新值 3: where lastname=upper('smith')
FIRSTNAME LASTNAME CITY
-------------------- -------------------- ------------------------
LEILA SMITH TALLAHASSEE
JENNIFER SMITH MORRISTOWN
上面的SQL语句先将lastname变量的值转换成大写，然后再与数据库中存储的值进行比较。
使用上面的方法，用户输入的时候不必考虑数据库中存储值的大小写形式，另外还可以
提高oracle服务器的效率，因为转换的只是输入条件，而不是整张表中的某列值。
initcap函数的语法格式：
initcap (c).c表示需要被转换的字符串，或者需要被转换的域。initcap函数会将要
转换的内容的每个单词的首字母转换成大写的形式，其他的是小写格式。这样方便阅读。
SQL> select first_name,last_name,commission_pct
2 from employees
3 where last_name=initcap(&lastname);
输入 lastname 的值: 'king'
原值 3: where last_name=initcap(&lastname)
新值 3: where last_name=initcap('king')
FIRST_NAME LAST_NAME COMMISSION_PCT
---------------------------------------- -------------------------------------------------- --------------
Steven King
Janette King .35
注意employees表中的first_name与last_name列的第一个字母是大写的。
substr函数的语法格式：
substr(c, p,l).c代表字符串或者需要处理的域，p代表开始字符串抽取的位置，l表示抽取的字符串长度。
customers表中的zip列的前三个字符有特殊的意义，表示顾客来自那个州。
现在把这些字符串抽取出来。以确定顾客来自那些州。
SQL> select distinct substr(zip,1,3)
2 from customers;
SUBSTR(ZIP,1,3)
------------------------
331
079
323
837
122
312
904
820
915
490
787
substr(zip,1,3),表示对zip列进行抽取，从第一个字符开始，抽取的长度是3.
SQL> select zip,substr(zip,1,3),substr(zip,-3,2)
2 from customers;
ZIP SUBSTR(ZIP,1,3) SUBSTR(ZIP,-3,2)
---------- ------------------------ ----------------
32328 323 32
90404 904 40
32306 323 30
83707 837 70
98115 981 11
12211 122 21
78710 787 71
82003 820 00
91510 915 51
30314 303 31
60606 606 60
字符串开始抽取的位置也可以是负数，表示从字符串反向开始计数。
substr(zip,-3,2)，表示从字符串倒数第三个字符开始抽取，长度是2.
instr函数的语法格式
强制的参数有：用于查找的字符串，待查找的子字符串（用单引号括起来）。
可选的参数有：用于字符串中开始查找的位置，用于表示匹配出现的次数。
如果查找到，则返回匹配开始处的第一个字符的位置，如果没有查找到则返回0.
SQL> select phone_number,instr(phone_number,'.') "第1个点的位置"，
2 instr(phone_number,'.',5) "从第5个字符开始"，
3 instr(phone_number,'.',1,2) "第2个点出现的位置"
4 from employees;
PHONE_NUMBER 第1个点的位置 从第5个字符开始 第2个点出现的位置
---------------------------------------- ------------- --------------- -----------------
515.123.4567 4 8 8
515.123.4568 4 8 8
515.123.4569 4 8 8
590.423.4567 4 8 8
590.423.4568 4 8 8
590.423.4569 4 8 8
590.423.4560 4 8 8
590.423.5567 4 8 8
515.124.4569 4 8 8
515.124.4169 4 8 8
515.124.4269 4 8 8
length函数的语法
length(c).c表示要求其长度的字符串或者域。
求customers表中地址栏的长度。
SQL> select length(address) "Length"
2 from customers;
Length
----------
12
13
11
18
12
12
16
12
13
17
11
length函数常用于确定表中的某一列是否，考虑应该增加长度了。
lpad函数的语法格式
lpad(c, l, s) c表示要处理的字符串，l表示填充以后字符串的长度（包括原始的数
据与填充符），s代表用于填充的特殊字符。lpad函数将在列的左边填充特定的字符，而不是空格。
SQL> select lastname,lpad(lastname,12,'#')
2 from customers;
LASTNAME LPAD(LASTNAME,12,'#')
---------- ------------------------------------------------
MORALES #####MORALES
THOMPSON ####THOMPSON
SMITH #######SMITH
PIERSON #####PIERSON
GIRARD ######GIRARD
CRUZ ########CRUZ
GIANA #######GIANA
JONES #######JONES
PEREZ #######PEREZ
要填充的字符串要用单引号括起来。
相应的有rpad与lpad在语法上是相同的，只不过实在要出来的字符串或者域的右边添
加特殊的字符。
replace函数的语法格式
replace(c, s, r).c代表要原来的字符串或者域，s表示要查找的字符串，r表示用来
代替s的字符串。replace函数会用r多次替换s，如果可以查找到多个s的话。
SQL> select address,replace(address,'P.O.','POST OFFICE')
2 from customers
3 where state='FL';
ADDRESS REPLACE(ADDRESS,'P.O.','POSTOF
-------------------- ------------------------------------
P.O. BOX 651 POST OFFICE BOX 651
P.O. BOX 66 POST OFFICE BOX 66
357 WHITE EAGLE AVE. 357 WHITE EAGLE AVE.
P.O. BOX 677 POST OFFICE BOX 677
要查找与要替换的字符串要用单引号括起来.
translate的使用
可以使用translate函数来查找并替换字符串中的单个字符，translate函数会替换多
次（如果存在需要替换的字符的话）直到字符串的末尾。
SQL> select translate('I love oracle database.','o','O')
2 from dual;
TRANSLATE('ILOVEORACLEDATABASE.','O','O')
----------------------------------------------
I lOve Oracle database.
字符串中所有出现的小写的o都会替换成大写的O.
SQL> select translate('I love oracle database.','Io','iO')
2 from dual;
TRANSLATE('ILOVEORACLEDATABASE.','IO','IO')
----------------------------------------------
i lOve Oracle database.
字符串中所有出现的小的o都会被替换成大小的O,大写的I都会被替换成小写的i。
即：o.....>O,I....>i
translate是字符级别的替换，replace是字符串级别的替换。比如在
translate('hello everybody.','body','oney')中b 都会替换成o字符，o字符都会
替换成n字符，d字符都会替换成e字符，y字符替换成字符y。
concat函数的语法格式
concat(c1, c2),c1表示第一个需要连接的字符串或者列，c2表示第二个需要连接的
字符串或者列。
SQL> select concat('I love ','oracle database.')
2 from dual;
CONCAT('ILOVE','ORACLEDATABASE.')
----------------------------------------------
I love oracle database.
ltrim函数的语法格式
ltrim(c, s).c 表示要处理的域，s表示左边需要移除的字符串。如果要处理的域中
没有发现要裁剪的字符串s，则不裁剪。rtrim的用法参照ltrim。
SQL> select ltrim('oracle nosql database','oracle')
2 from dual;
LTRIM('ORACLENOSQLDATABASE','O
------------------------------
nosql database
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26110315/viewspace-716097/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/26110315/viewspace-716097/
扫一扫
1422
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
