【函數】oracle函數系列（1）--字元函數
各位技術愛好者，看完本文後，你可以掌握如下的技能，也可以學到一些其它你所不知道的知識，~o(∩_∩)o~：
① 常見字元函數（lower、upper、initcap、concat、substr、length、intr、lpad、rpad、trim、chr、ascii、replace、translate）的使用
② 判斷字元串中是否含有漢字
③ substr和instr的聯合使用
④ 替換函數的使用
tips：
① 本文在itpub（http://blog.itpub.net/26736162）和微信公衆号（xiaomaimiaolhr）上有同步更新。
② 文章中用到的所有代碼、相關軟體、相關資料及本文的pdf版本都請前往小麥苗的雲盤下載下傳，小麥苗的雲盤位址見：http://blog.itpub.net/26736162/viewspace-1624453/。
③ 若網頁文章代碼格式有錯亂，請嘗試以下辦法：①去部落格園位址閱讀，②下載下傳pdf格式的文檔來閱讀。
④ 在本篇blog中，代碼輸出部分一般放在一行一列的表格中。其中，需要特别關注的地方我都用灰色背景和粉紅色字型來表示，比如在下邊的例子中，thread 1的最大歸檔日志号為33，thread 2的最大歸檔日志号為43是需要特别關注的地方；而指令一般使用黃色背景和紅色字型标注；對代碼或代碼輸出部分的注釋一般采用藍色字型表示。
list of archived logs in backup set 11
thrd seq low scn low time next scn next time
---- ------- ---------- ------------------- ---------- ---------
1 32 1621589 2015-05-29 11:09:52 1625242 2015-05-29 11:15:48
1 33 1625242 2015-05-29 11:15:48 1625293 2015-05-29 11:15:58
2 42 1613951 2015-05-29 10:41:18 1625245 2015-05-29 11:15:49
2 43 1625245 2015-05-29 11:15:49 1625253 2015-05-29 11:15:53
[zhlhrdb1:root]:/>lsvg -o
t_xlhrd_app1_vg
rootvg
[zhlhrdb1:root]:/>
00:27:22 sql> alter tablespace idxtbs read write;
====》2097152*512/1024/1024/1024=1g
本文如有錯誤或不完善的地方請大家多多指正，itpub留言或qq皆可，您的批評指正是我寫作的最大動力。
【推薦】 oracle中如何判斷一個字元串是否含有漢字
http://blog.itpub.net/26736162/viewspace-1688209/
oracle中如何判斷一個字元串是否含有漢字（2）
http://mp.weixin.qq.com/s?__biz=mzizota2njeznq==&mid=2454771569&idx=1&sn=5bb10af43556c18906540ffe76ba87cf&chksm=fe8bba09c9fc331f0152b38067150920e75bac8cdd13fd1a2d881fdfec835ef152b54e2c798c&scene=21#wechat_redirect
oracle中如何判斷一個字元串是否含有漢字
http://mp.weixin.qq.com/s?__biz=mzizota2njeznq==&mid=2454771284&idx=1&sn=ab4e725c4d4ad448e245e1668ccac382&scene=21#wechat_redirect
【函數】wm_concat包的訂制
http://mp.weixin.qq.com/s?__biz=mzizota2njeznq==&mid=2454771521&idx=1&sn=e75d27d085892a4be051c4c2e736f97a&chksm=fe8bba39c9fc332f3a2af83918cdb2afa1c83a7faf15971ce566c4321540c38e51279dd615a2&scene=21#wechat_redirect
最近因為360雲盤停止個人使用者服務的事情要忙，是以等待事件系列就不更新了，轉而更新函數系列吧，畢竟函數比較簡單嘛。
本篇文章為第一篇，講解所有的字元函數。
先來一道考題：
which four are types of functions available in sql? (choose 4)
a. string
b. character
c. integer
d. calendar
e. numeric
f. translation
g. date
h. conversion
answer: b,e,g,h
字元函數是 oracle 中最常用的函數，我們來看看有哪些字元函數：
（一）大小寫控制函數
l lower(char)：将字元串全部轉化為小寫的格式。
l upper(char)：将字元串全部轉化為大寫的格式。
l initcap('sql course') 每個單詞的首字母大寫，其餘變為小寫，結果：sql course，
（二）字元控制函數
l concat('hello','world') 字元串連接配接，結果:helloworld
l length(char)：傳回字元串的長度。
l substr(char,m,n)：取字元串的子串，m表示起點，n 代表取 n 個字元的意思
l replace(char1,search_string,replace_string) 替換
l instr(char1,char2,[,n[,m]]) 取子串在字元串的位置(特别取某一個特殊字元在原字元串中的位置)
l trim(' hello world '), 結果為： “hello world”
l ltrim(' hello world ')，結果為： “hello world ”
l rtrim(' hello world ')，結果為： “ hello world”
l lpad(salary,10,'*') 結果：*****24000
l rpad(salary, 10, '*') 結果：24000*****
l chr()函數将ascii碼轉換為字元： ascii碼 –》 字元；
l ascii()函數将字元轉換為ascii碼：字元 –》 ascii碼；
sql> select lower('hello world') from dual;
lower('hell
-----------
hello world
sql> select ename,lower(ename) from scott.emp;
ename lower(enam
---------- ----------
smith smith
allen allen
ward ward
jones jones
martin martin
blake blake
clark clark
scott scott
king king
turner turner
adams adams
james james
ford ford
miller miller
經常用于比較字元串的内容：
sql> select upper('hello world') from dual;
upper('hell
sql> select ename,sal,hiredate from scott.emp where lower(ename) = 'ward';
ename sal hiredate
---------- ---------- -------------------
ward 1250 1981-02-22 00:00:00
select initcap('sql course ') from dual;
結果：
sql> select concat(empno,ename) from scott.emp;
concat(empno,ename)
--------------------------------------------------
7369smith
7499allen
7521ward
7566jones
7654martin
7698blake
7782clark
7788scott
7839king
7844turner
7876adams
7900james
7902ford
7934miller
14 rows selected.
sql> select concat('a','b') from dual;
co
--
ab
sql> select concat(concat('a','b'),'c') from dual;--可以嵌套使用
con
---
abc
可以使用連字元 || 來連接配接多列：
select 'a'||'b' from dual;
下标從1開始，這個函數有三個參數，允許你将目标字元串的一部份輸出，第一個參數為目标字元串，第二個參數是将要輸出的子串的起點，第三個參數是将要輸出的子串的長度，如果沒有第三個參數，則餘下的字元全部輸出。
注意：如果第二個參數為負數 那麼将會從目标字元串的尾部開始向前定位至負數的絕對值的位置
select substr(ename,2) from emp--從第2個位置截到末尾
select substr(ename,2,3) from emp--從第2個位置截取3個字母
select substr('lihuarong',0,3) col_1,
substr('lihuarong',1,3)col_2,
substr('lihuarong',3)col_3,
substr('lihuarong',-4,3) col_4
from dual;
sys@raclhr1> select substr('lihuarong',0,3) col_1,
2 substr('lihuarong',1,3)col_2,
3 substr('lihuarong',3)col_3,
4 substr('lihuarong',-4,3) col_4
5 from dual;
col col col_3 col
--- --- ------- ---
lih lih huarong ron
注意： 如果第二個參數大于第一個參數的長度，則第一個參數從該列預設的長度開始計算。如下例的name列預設的長度為10
select name,substr(name,-4,3),
substr(name,-5,3)
select length('重慶貴陽30n重慶貴陽30n-77623') from dual;
select length(77623) from dual;
select length(sysdate),sysdate from dual;
select length(1/2) from dual;
select length('1/2'),
length(1 / 2),
1 / 2,
length(0.5)
from dual;
the length functions return the length of char. length calculates length usingcharacters as defined by the input character set. --傳回以字元為機關的長度.
lengthb usesbytes instead of characters. --傳回以位元組為機關的長度.
lengthc usesunicode complete characters. --傳回以unicode完全字元為機關的長度.
length2 usesucs2 code points.--傳回以ucs2代碼點為機關的長度.
length4 usesucs4 code points. --傳回以ucs4代碼點為機關的長度.
char can be any of the data types char, varchar2, nchar, nvarchar2, clob, or nclob. the exceptions are lengthc, length2, and length4, which do not allow char to be a clob or nclob. the return value is of data type number. if char has data type char, then the length includes all trailing blanks. if char is null, then this function returns null.
restriction on lengthb the lengthb function is supported for single-byte lobs only. it cannot be used with clob and nclob data in a multibyte character set.
examples
the following example uses the length function using a single-byte database character set:
select length('candide') "length in characters"
from dual;
length in characters
--------------------
7
the next example assumes a double-byte database character set.
select lengthb ('candide') "length in bytes"
length in bytes
---------------
14
sql> select length('北京') from dual;
length('北京')
--------------
2
sql> select lengthb('北京') from dual;
lengthb('北京')
6
sql> select lengthb('beijing') from dual;
lengthb('beijing')
------------------
7
sql> select length('beijing') from dual;
length('beijing')
-----------------
7
值得一提的是：
在不同的資料庫，因為字元集的不同，lengthb得到的值可能會不一樣。如zhs16gbk采用兩個byte位來定義一個漢字。而在utf8，采用3個byte。
--檢視字元集語句
sql> select * from nls_database_parameters where parameter='nls_characterset';
parameter value
select lengthb('齊'),length('齊') from dual
在計算單位元組的字元串時。傳回的值是相同的。
select lengthb('string'),length('string') from dual
2 、通過對同一個字元串的長度判斷
lengthb(string)=length(string)
可以判斷是否含有漢字。
例子
select lengthb('齊'),
length('齊')
select l.code,
length(l.code),
lengthb(l.code),
length2(l.code),
length4(l.code),
lengthc(l.code)
from xb_link l
where length(l.code) != lengthb(l.code)
and length(l.code) < 20;
參考：
3.0.7 instr :相當于string類中的indexof，求索引
如果需要知道在一個字元串中滿足特定的内容的子串位置可以使用instr，它的第一個參數是目标字元串， 第二個參數是比對的内容，第三和第四個參數是數字，用以指定開始搜尋的起點以及指出第幾個滿足條件的将會被傳回。
預設第三個與第四個參數的數值均為1，如果第三個數值為負數那麼将會從後向前搜尋。
如果未比對到則傳回0；
select instr('hellowholdhh', 'h', 6, 2) l1, --從第6個位置開始，第2次出現h的位置
instr('hellowholdhh', 'h') l2, --從第1個位置開始，第1次出現h的位置
instr('hellowholdhh', 'h', 2) l3, --從第2個位置開始，第1次出現h的位置
instr('hellowholdhh', 'h', -1) l4, --從倒數第1個位置開始從右往左搜尋，第1次出現h的位置
instr('hellowholdhh','/') l5--從第1個位置開始，第1次出現h的位置，如果比對不到則傳回0
sys@raclhr1> select instr('hellowholdhh', 'h', 6, 2) l1, --從第6個位置開始，第2次出現h的位置
2 instr('hellowholdhh', 'h') l2, --從第1個位置開始，第1次出現h的位置
3 instr('hellowholdhh', 'h', 2) l3, --從第2個位置開始，第1次出現h的位置
4 instr('hellowholdhh', 'h', -1) l4, --從倒數第1個位置開始從右往左搜尋，第1次出現h的位置
5 instr('hellowholdhh','/') l5--從第1個位置開始，第1次出現h的位置，如果比對不到則傳回0
6 from dual;
l1 l2 l3 l4 l5
---------- ---------- ---------- ---------- ----------
12 1 7 12 0
注意：無論是從前向後還是從後向前搜尋，傳回的數值都是從左到右的數值。
select * from dba_objects d where instr('5,6,7', d.object_id) > 0;
相當于：
select * from dba_objects d where d.object_id=5 or d.object_id=6 or d.object_id=7;
這2個函數常常關聯使用，但是存在如果instr比對不到字元串的話傳回的就是0，這樣substr得到的值就是空值，是以這個時候就應該進行轉換，使用decode或其它函數來轉化。
select name,
substr(name, 1, instr(name, 'a') - 1),
decode(instr(name, 'a'),
0,
name,
substr(name, 1, instr(name, 'a') - 1))
from xb_b;
表示補充的含義，這兩個函數最少需要兩個參數，最多需要三個參數，第一個參數是需要處理的字元串，第二個參數是需要将字元串擴充的寬度，第三個參數表示加寬部分用什麼字元來做填補，第三個參數的預設值為空格，但也可以是單個的字元或字元串
select lpad(sal,10,'*') from emp
select rpad(sal,10,'*') from emp
将sal顯示為10位,不足的位置補上字元*
補充：如果限定的位數在比字元的個數少，那麼截取字元串的右側字母
select ename,lpad(ename,5,'#') from emp;
select lpad(name,10),lpad(name,10,'*'),lpad(value,10,'*'),lpad(name,2,'&'),
rpad(name,10),rpad(name,10,'*'),rpad(value,10,'*'),rpad(name,2,'&')
ltrim和rtrim至少需要一個參數最多允許兩個參數第一個參數與lpad和rpad類似是一個字元串 第二個參數也是一個字元或字元串預設則是空格 如果第二個參數不是空格的話 那麼該函數将會像剪除空格那樣剪除所指定的字元
使用ltrime和rtrim過濾一邊的空格
select ltrim(' a b c ') l ,rtrim(' hello world ') r,trim(' hello world ') from dual;
sql> select ltrim(' a b c ') l ,rtrim(' hello world ') r,trim(' hello world ') from dual;
l r trim('hellowor
----------- ------------------ --------------
a b c hello world hello world
trim([ { { leading | trailing | both }
[ trim_character ]
| trim_character
}
from
]
trim_source
)
select trim(' a|a ') c1,
trim(both from(' a|a ')) c2,
trim(trailing from(' a|a ')) c3,
trim(leading from(' a|a ')) c4,
trim('x' from 'xx a|a xx') c5,
trim(both 'x' from 'xx a|a xx') c6,
trim(trailing 'x' from 'xx a|a xx') c7,
trim(leading 'x' from 'xx a|a xx') c8,
rtrim('xyxxxy a|a xxxy', 'xy') c9,
ltrim('xyxxxy a|a xxxy', 'xy') c10,
trim(both from replace(' a|a', '|', '')) c11,
trim(both ' ' from replace(' a|a', '|', '')) c12,
trim(both 'a' from replace(' a|a', '|', '')) c13
both參數表示同時去除字元串前後所指定的内容（預設情況下删除空格）。“trailing”參數可以完成字元串尾部空格的删除功能，而“leading”參數可以完成字元串頭部空格的删除功能。也就是說，使用“trailing”和“leading”參數可以指定空格的删除位置。
sql> select trim(' a|a ') c1,
2 trim(both from(' a|a ')) c2,
3 trim(trailing from(' a|a ')) c3,
4 trim(leading from(' a|a ')) c4,
5 trim('x' from 'xx a|a xx') c5,
6 trim(both 'x' from 'xx a|a xx') c6,
7 trim(trailing 'x' from 'xx a|a xx') c7,
8 trim(leading 'x' from 'xx a|a xx') c8,
9 rtrim('xyxxxy a|a xxxy', 'xy') c9,
10 ltrim('xyxxxy a|a xxxy', 'xy') c10,
11 trim(both from replace(' a|a', '|', '')) c11,
12 trim(both ' ' from replace(' a|a', '|', '')) c12,
13 trim(both 'a' from replace(' a|a', '|', '')) c13
14 from dual;
c1 c2 c3 c4 c5 c6 c7 c8 c9 c10 c1 c1 c
--- --- ------ ----- ------- ------- --------- --------- ------------- ----------- -- -- -
a|a a|a a|a a|a a|a a|a xx a|a a|a xx xyxxxy a|a a|a xxxy aa aa
chr()函數将ascii碼轉換為字元：ascii碼 –》 字元；
ascii()函數将字元轉換為ascii碼： 字元 –》 ascii碼；
在oracle中chr()函數和ascii()是一對反函數。
sql> select chr(65) from dual;
chr(65)
-------
a
sql> select ascii('a') from dual;
ascii('a')
----------
65
select chr(5),
chr(10), --換行
chr(13) --回車
oracle常用替換函數有2個，分别是replace和translate函數。replace與translate都是替代函數，隻不過replace針對的是字元串，而translate針對的是單個字元，下邊我們分别講解這2個函數的用法。
它的工作就如果它的名字所說的那樣，該函數需要三個參數，第一個參數是需要搜尋的字元串，第二個參數是搜尋的内容，第三個參數則是需要替換成的字元串，如果第三個參數省略，則預設為空格，如果第二個參數是null，那麼将隻執行搜尋操作而不會替換任何内容。
select replace('helloworld','o','a') from dual;
select ename ,replace(ename,'a','b') from emp;
select replace('lihuarong','ua','aa') col_1,
replace('lihuarong','on')col_2,
replace('lihuarong',null) col_3
sys@raclhr1> select replace('lihuarong','ua','aa') col_1,
2 replace('lihuarong','on') col_2,
3 replace('lihuarong',null) col_3
4 from dual;
col_1 col_2 col_3
--------- ------- ---------
lihaarong lihuarg lihuarong
文法：translate(char, from, to)
用法：
1. 傳回将出現在from中的每個字元替換為to中的相應字元以後的字元串。
2. 若from比to字元串長，那麼在from中比to中多出的字元将會被删除，或者認為from中多出的字元在to中與空對應
3. 三個參數中有一個是空，傳回值也将是空值。
舉例：sql> select translate('abcdefga','abc','wo') 傳回值 from dual;
傳回值
-------
wodefgw
分析：該語句要将'abcdefga'中的'abc'轉換為'wo',由于'abc'中'a'對應'wo'中的'w',故将'abcdefga'中的'a'全部轉換成'w'; 而'abc'中'b'對應'wo'中的'o',故将'abcdefga'中的'b'全部轉換成'o';'abc'中的'c'在'wo'中沒有與之對應的字元,故将'abcdefga'中的'c'全部删除;
簡單說來，就是将from中的字元轉換為to中與之位置對應的字元，若to中找不到與之對應的字元，傳回值中的該字元将會被删除。
09:43:50 sql> select translate('abcdefga','abc','wo') from dual;
transla
wodefgw
elapsed: 00:00:00.14
09:43:57 sql> select translate('abcdefga','abc','') from dual;
t
-
elapsed: 00:00:00.00
select translate('ab 你好 bcadefg','abcdefg','1234567'),translate('ab 你好 bcadefg','1abcdefg','1') from dual;
1、 按數字和字母混合字元串中的字母排序
09:52:01 sql> create or replace view v as select empno || ' '||ename as data from scott.emp;
view created.
elapsed: 00:00:00.54
09:52:07 sql> select * from v
09:52:15 2 ;
data
---------------------------------------------------
9000 lastwiner
9001 lastwiner
7369 smith
7499 allen
7521 ward
7566 jones
7654 martin
7698 blake
7782 clark
7788 scott
7839 king
7844 turner
7876 adams
7900 james
7902 ford
7934 miller
16 rows selected.
elapsed: 00:00:00.20
09:55:07 sql> select data,translate(data,'- 0123456789','-') from v order by 2;
data translate(data,'-0123456789','-')
--------------------------------------------------- -----------------------------------------------------
7876 adams adams
7499 allen allen
7698 blake blake
7782 clark clark
7902 ford ford
7900 james james
7566 jones jones
7839 king king
7654 martin martin
7934 miller miller
7788 scott scott
7369 smith smith
7844 turner turner
7521 ward ward
9001 lastwiner lastwiner
9000 lastwiner lastwiner
2、 從字元串中删除不需要的字元
去掉aeiou字元：
select a.ename,translate(a.ename,'1aeiou','1'),regexp_replace(a.ename,'[aeiou]') from scott.emp a where a.deptno=10;
3、 将字元和數字分離(隻保留數字、隻保留數字以外的其它字元)
drop table dept2 purge;
create table dept2 as
select dname || deptno as data from scott.dept;
select a.data,
regexp_replace(a.data, '[0-9]') dname,
regexp_replace(a.data, '[^0-9]') deptno,
translate(a.data, 'a0123456789', 'a') dname,
translate(a.data, '0123456789' || data, '0123456789') deptno
from dept2 a;
4、 提取姓名的大寫首字母縮寫
create or replace view v as
select 'michael hartstein' as al from dual;
select regexp_replace(v.al,'([[:upper:]])(.*)([[:upper:]])(.*)','\1.\3') as sx from v;
select translate(al,' '||a2,'.') as sx from (select al ,lower(al) as a2 from v);
總結：replace與translate都是替代函數，隻不過replace針對的是字元串，而translate針對的是單個字元。
問題：将所有員工的名字按小寫的方式顯示
sql> select lower(ename) from emp;
問題：将所有員工的名字按大寫的方式顯示。
sql> select upper(ename) from emp;
問題：顯示正好為 5 個字元的員工的姓名。
sql> select * from emp where length(ename)=5;
問題：顯示所有員工姓名的前三個字元。
sql> select substr(ename,1,3) from emp;
問題：以首字母大寫,後面小寫的方式顯示所有員工的姓名。
sql> select upper(substr(ename,1,1)) ||
lower(substr(ename,2,length(ename)-1))from emp;
問題：以首字母小寫,後面大寫的方式顯示所有員工的姓名。
sql> select lower(substr(ename,1,1)) ||
upper(substr(ename,2,length(ename)-1))from emp;
問題：顯示所有員工的姓名，用“我是老虎”替換所有“a”
sql> select replace(ename,'a', '我是老虎') from emp;
如下表（表名 ：lhr_str）：需要将name和code重新命名，規則為：将第一個‘/’之前的内容換成“a端原命名-z端原命名”，如果沒有‘/’則不更改！
begin
for cr in (select * from lhr_str) loop
update lhr_str l
set l.new_name = l.a端原命名 || '-' || l.z端原命名 ||
substr(l.name, instr(l.name, '/'))
where instr(l.name, '/') <> 0; --這裡一定要加where條件否則全部更新，下面的類似
set l.new_code = l.a端原命名 || '-' || l.z端原命名 ||
substr(l.code, instr(l.code, '/'))
where instr(l.code, '/') <> 0;
set l.new_name = l.name
where instr(l.name, '/') = 0;
set l.new_code = l.code
where instr(l.code, '/') = 0;
end loop;
end;