ITPub博客

首页 > Linux操作系统 > Linux操作系统 > oracle中的单行函数(一)

oracle中的单行函数(一)

原创 Linux操作系统 作者:pingley 时间:2012-02-12 21:48:12 0 删除 编辑
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/,如需转载,请注明出处,否则将追究法律责任。

下一篇: 探索oracle dual表
请登录后发表评论 登录
全部评论

注册时间:2012-02-06

  • 博文量
    169
  • 访问量
    741090