该实验的目的是掌握常用的字符串操作的函数
字符串的大小写操作
SQL> select lower(ename),upper(ename),initcap(ename) from emp;
(显示小写) (显示大写) (显示首字母大写)
LOWER(ENAM UPPER(ENAM INITCAP(EN
---------- ---------- ----------
king KING King
scott SCOTT Scott
clark CLARK Clark
blake BLAKE Blake
martin MARTIN Martin
smith SMITH Smith
jones JONES Jones
ward WARD Ward
allen ALLEN Allen
turenr TURENR Turenr
miller MILLER Miller
fdrd FDRD Fdrd
james JAMES James
adams ADAMS Adams
将两个字段连接起来
SQL> select ename,job,concat(ename,job) from emp;
ENAME JOB CONCAT(ENAME,JOB)
---------- --------- -------------------
KING PRESIDENT KINGPRESIDENT
SCOTT ANAKYST SCOTTANAKYST
CLARK MANAGER CLARKMANAGER
BLAKE MANAGER BLAKEMANAGER
MARTIN SAKESMAN MARTINSAKESMAN
SMITH CLERK SMITHCLERK
JONES MANAGER JONESMANAGER
WARD SALESMAN WARDSALESMAN
ALLEN SALESMAN ALLENSALESMAN
TURENR SALESMAN TURENRSALESMAN
MILLER CLERK MILLERCLERK
FDRD ANALYST FDRDANALYST
JAMES CLERK JAMESCLERK
ADAMS CLERK ADAMSCLERK
已选择14行。
按照子的个数
SQL> select length('张飞') from dual;
LENGTH('张飞')
--------------
2
按照字的字节数
SQL> select lengthb('张飞') from dual;
LENGTHB('张飞')
---------------
4
按照字的长度
SQL> select lengthc('张飞') from dual;
LENGTHC('张飞')
---------------
2
SQL> select ename,substr(ename,1,1) "first",substr(ename,-1) "last" from emp;
ENAME fi la
---------- -- --
KING K G
SCOTT S T
CLARK C K
BLAKE B E
MARTIN M N
SMITH S H
JONES J S
WARD W D
ALLEN A N
TURENR T R
MILLER M R
FDRD F D
JAMES J S
ADAMS A S
已选择14行。
substr(字符串,m,n),m表示(从左到右)第几个字符开始,为负数是从(从右到左)第几个字符开始,N是数几个字符,没事表示到最后。
SQL> select ename,instr(ename,'A') "A在第几位" from emp;
ENAME A在第几位
---------- ----------
KING 0
SCOTT 0
CLARK 3
BLAKE 3
MARTIN 2
SMITH 0
JONES 0
WARD 2
ALLEN 1
TURENR 0
MILLER 0
FDRD 0
JAMES 2
ADAMS 1
已选择14行。
instr表示某个字符,在所有的字符中的位置,零表示没有字符在其中。
截断字符串和添加字符串的函数
SQL> select trim(leading 'a' from 'aaaaaaaaaaasdsdaa') from dual;
TRIM(L
------
sdsdaa
leading 表示 截掉前面的a
SQL> select trim(trailing 'a' from 'aaaaaaaaaaasdsdaaaaaa') from dual;
TRIM(TRAILING'A
---------------
aaaaaaaaaaasdsd
trailing截掉最后面的a
SQL> select trim(both 'a' from 'aaaaaaaaaaasdsdaaaaaa') from dual;
TRIM
----
sdsd
both截掉连续的前面和后面的a
SQL> select trim ('a' from 'aaaadssadsaa') from dual;
TRIM('
------
dssads
trim表示截头去尾(包括空格在乃)
SQL> select lpad(ename,20,'_') ename,rpad(ename,20,'_') ename from emp;
ENAME ENAME
---------------------------------------- ----------------------------------------
________________KING KING________________
_______________SCOTT SCOTT_______________
_______________CLARK CLARK_______________
_______________BLAKE BLAKE_______________
______________MARTIN MARTIN______________
_______________SMITH SMITH_______________
_______________JONES JONES_______________
________________WARD WARD________________
_______________ALLEN ALLEN_______________
______________TURENR TURENR______________
______________MILLER MILLER______________
________________FDRD FDRD________________
_______________JAMES JAMES_______________
_______________ADAMS ADAMS_______________
已选择14行。
左铺垫和又铺垫 20是总共铺垫到多少位,_是要铺垫的字符串。
SQL> select replace('JACK and JUE' ,'J','BL') from dual;
REPLACE('JACKA
--------------
BLACK and BLUE
replace表示 将字符串中的J全部替换成BL.
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/25607042/viewspace-691305/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/25607042/viewspace-691305/