Oracle數(shù)據(jù)庫(kù)中的字符處理技巧
一、去掉尾部的空格字符。
有時(shí)候在查詢或者進(jìn)行其他處理的時(shí)候，需要把字符串尾部的空格字符去掉。如有時(shí)候應(yīng)用軟件設(shè)計(jì)的不合理，會(huì)把空格字符保存在數(shù)據(jù)庫(kù)中。如在輸入產(chǎn)品品號(hào)的時(shí)候，用戶不小心，把“DT001”輸成了“DT001 ”。如果應(yīng)用程序在設(shè)計(jì)的時(shí)候，能夠自動(dòng)把尾部的空格去掉然后在保存到數(shù)據(jù)庫(kù)中能夠就萬(wàn)無(wú)一失了。但是不少的應(yīng)用軟件在開發(fā)的時(shí)候沒有如此設(shè)計(jì)。這就給后續(xù)的處理帶來(lái)了很多的麻煩。因?yàn)槔肳here語(yǔ)句來(lái)查找記錄的時(shí)候，“DT001”(最后不帶空格)與“DT001 ”(最后帶一個(gè)空格)兩個(gè)是不同的條件。如果想利用這個(gè)條件來(lái)進(jìn)行數(shù)據(jù)更新、查詢等等，就會(huì)遇到問題。為此在寫相關(guān)的Update或者Select語(yǔ)句的時(shí)候，可能需要把后面的空格符號(hào)去掉。為此在Oracle數(shù)據(jù)庫(kù)中，有很多種解決方式。如數(shù)據(jù)庫(kù)管理員可以使用Ltrim函數(shù)來(lái)實(shí)現(xiàn)。這個(gè)函數(shù)的格式為L(zhǎng)trim[c1,c2]。其作用是去掉C1左邊所包含的C2種的任何字符。當(dāng)遇到不是C2種的字符串時(shí)結(jié)束，然后返回剩余的字符串。如果把C2字符串設(shè)置為空格符號(hào)(默認(rèn)情況下就是空格)，那么就可以把DT001后面的空格符號(hào)去掉了。
二、在頭部自動(dòng)進(jìn)行填充。
有時(shí)候可能數(shù)據(jù)庫(kù)設(shè)計(jì)的時(shí)候，考慮的不夠周到，導(dǎo)致某些字段不夠大。在對(duì)數(shù)據(jù)庫(kù)進(jìn)行升級(jí)的時(shí)候，需要調(diào)整相關(guān)的字段。此時(shí)就可能需要對(duì)某個(gè)字段的頭部進(jìn)行填充，以達(dá)到數(shù)據(jù)一致性的要求。如現(xiàn)在有個(gè)ERP系統(tǒng)，其需要用到一張產(chǎn)品信息的表，其中有一個(gè)產(chǎn)品編號(hào)字段。剛開始在設(shè)計(jì)產(chǎn)品編碼的時(shí)候，設(shè)計(jì)的長(zhǎng)度不夠，如只設(shè)置了5位。隨著企業(yè)產(chǎn)品記錄的增多，需要對(duì)這個(gè)編碼的內(nèi)容進(jìn)行擴(kuò)展。如產(chǎn)品信息屬于包裝材料類的，需要在原來(lái)產(chǎn)品編號(hào)的頭部加入一個(gè)字符B;如產(chǎn)品信息屬于客供品的，則需要在原來(lái)產(chǎn)品編號(hào)的頭部加入一個(gè)字符C;如產(chǎn)品信息屬于輔助材料的，則在原來(lái)的產(chǎn)品編號(hào)前面加入一個(gè)字符F;等等。此時(shí)該如何實(shí)現(xiàn)這個(gè)需求呢?難道要一個(gè)個(gè)去修改嗎?現(xiàn)在這手工修改的工作量比較大，而且容易出錯(cuò)，這個(gè)方法不可取。其實(shí)在Oracle數(shù)據(jù)庫(kù)系統(tǒng)中提供了一個(gè)單行字符函數(shù)，可以幫助數(shù)據(jù)庫(kù)管理員與企業(yè)來(lái)解決這個(gè)問題。這個(gè)函數(shù)就是RPAD函數(shù)。這個(gè)命令的格式為RPAD(C1，N，C2)。這個(gè)函數(shù)的意識(shí)是在C1的右邊填充字符C2，直到字符串的總長(zhǎng)度滿足N。默認(rèn)情況下C2的值為空格，用戶可以根據(jù)自己的需要設(shè)置這個(gè)值。如果C1的長(zhǎng)度比N要大，則會(huì)截取C1右邊的N個(gè)字符。現(xiàn)在如果要實(shí)現(xiàn)上面這個(gè)需求，則只需要設(shè)置函數(shù)RPAD(產(chǎn)品編號(hào)，6，F(xiàn))即可。由于原來(lái)的產(chǎn)品編號(hào)為5位，現(xiàn)在需要為輔助信息的產(chǎn)品編號(hào)前面加入一個(gè)字符F，修改后的總長(zhǎng)度變?yōu)?位。不過在使用這個(gè)函數(shù)的話，往往需要利用Where條件語(yǔ)句進(jìn)行限制。
現(xiàn)在這個(gè)函數(shù)還可以里用實(shí)現(xiàn)字符串的截取。如還是這個(gè)產(chǎn)品編號(hào)，其前面一位表示產(chǎn)品的種類。此時(shí)數(shù)據(jù)庫(kù)管理員就可以利用RPAD(產(chǎn)品編號(hào)，1)來(lái)截取產(chǎn)品編號(hào)的第一位字符(這里產(chǎn)品編號(hào)的長(zhǎng)度比1要大，則會(huì)截取產(chǎn)品右邊的1個(gè)字符，即產(chǎn)品類別標(biāo)示碼)。這在報(bào)表設(shè)計(jì)的時(shí)候也非常有用，有利于簡(jiǎn)化報(bào)表的顯示。
三、字符大小寫的控制。
在做報(bào)表的時(shí)候，我們可能需要對(duì)字符串的現(xiàn)實(shí)格式進(jìn)行控制，如大小寫的顯示格式。另外，在進(jìn)行數(shù)據(jù)庫(kù)移植的時(shí)候，可能以前的收入大小寫不規(guī)范。數(shù)據(jù)庫(kù)管理員需要采用一定的規(guī)則來(lái)規(guī)范移植后的數(shù)據(jù)庫(kù)字符串書寫規(guī)范。為了簡(jiǎn)化這些需求的實(shí)現(xiàn)方式，在Oracle數(shù)據(jù)庫(kù)中也提供了一些工具來(lái)解決這些問題。
如以前在輸入產(chǎn)品編號(hào)的時(shí)，可能大小寫不區(qū)分。而在前臺(tái)應(yīng)用程序中也沒有進(jìn)行這方面的控制?，F(xiàn)在用戶希望所有的產(chǎn)品編號(hào)都為大寫，需要把原先是小寫的產(chǎn)品編號(hào)全部轉(zhuǎn)換成大寫。此時(shí)如果產(chǎn)品數(shù)量比較多的話，顯然利用手工修改的方式會(huì)增加工作量。如果編寫一個(gè)程序來(lái)實(shí)現(xiàn)的話，又太復(fù)雜，其實(shí)我們可以借鑒Oracle數(shù)據(jù)庫(kù)提供的字符串處理函數(shù)來(lái)實(shí)現(xiàn)。在單行字符函數(shù)集中，有一個(gè)函數(shù)UPPER，它的作用就是將全部字符串都改寫為大寫并返回。為此我們可以利用一個(gè)子查詢或者借助視圖的幫助，把所有產(chǎn)品編號(hào)中，如果含有小寫字符的產(chǎn)品編號(hào)都更新為大寫。如果原來(lái)就是大寫的，就保持原樣(而不是說(shuō)原來(lái)大寫的變小寫，原來(lái)小寫的變大寫)。同理，既然小寫可以變大寫，那么大寫也就可以變?yōu)樾?。利用單行字符串函?shù)LOWER，就可以把全部字符串改為小寫輸出(把大寫字母改為小寫，而小寫字符保持不變)。
另外一個(gè)大小寫的控制原則比較復(fù)雜，如對(duì)于一些英文地址或者英文名字之類的字符串，需要首個(gè)字母大寫，而其他字符小寫。這個(gè)實(shí)現(xiàn)起來(lái)就有一定的難度。還好在Oracle數(shù)據(jù)庫(kù)中提供了一個(gè)現(xiàn)成的解決方法，即利用Initchar函數(shù)來(lái)實(shí)現(xiàn)。這個(gè)函數(shù)的功能是將某個(gè)字段中每個(gè)單詞的首字符轉(zhuǎn)換為大小，其他字符都是小寫的字符串。通常情況下，這個(gè)字段之間的單詞可以利用空格、控制字符或則標(biāo)點(diǎn)符號(hào)來(lái)表示?？梢娡ㄟ^這個(gè)三個(gè)大小寫字符串控制函數(shù)，可以優(yōu)化字符串的顯示格式，讓其顯示更加的規(guī)范。而結(jié)合Update語(yǔ)句的話，還可以成批的在數(shù)據(jù)庫(kù)中進(jìn)行更新，讓其保存的數(shù)據(jù)也符合規(guī)范化的要求。
四、字符串中的復(fù)雜替換。
幾年之后，杭州進(jìn)行來(lái)一些行政區(qū)劃的規(guī)格，如把下沙市改為了直屬杭州管轄的下沙區(qū)。為此有一家企業(yè)的數(shù)據(jù)庫(kù)管理人員就問筆者，能否把一些記錄中的下沙市都改為下沙區(qū)。什么意思呢?也就是說(shuō)，在數(shù)據(jù)庫(kù)中可有含有如下記錄“下沙市某某鋼鐵廠”、“杭州市某某服裝廠”、“濱江區(qū)某某信息有限公司”等等。現(xiàn)在數(shù)據(jù)庫(kù)管理員希望把含下沙市的內(nèi)容都改為下沙區(qū)。這涉及到一些復(fù)雜字段的替換，實(shí)現(xiàn)起來(lái)具有一定的難度。數(shù)據(jù)庫(kù)是結(jié)構(gòu)化數(shù)據(jù)的結(jié)合體，不比Word文檔，可以利用查找替換功能來(lái)解決這個(gè)需求。那么在Oracle數(shù)據(jù)庫(kù)中該如何實(shí)現(xiàn)呢?在Oracle數(shù)據(jù)庫(kù)中就模擬實(shí)現(xiàn)了Word文檔中的查找替換功能。
如在單行字符函數(shù)中有一個(gè)叫做Replace的函數(shù)，就可以幫助數(shù)據(jù)庫(kù)管理員實(shí)現(xiàn)這個(gè)需求。這個(gè)函數(shù)的格式為Replace(C1，C2，C3)。他的含義就彪死后把C1中出現(xiàn)的C2都替換成C3，然后返回字符串。這是什么意思呢?其實(shí)就是跟Word文檔中的查找替換功能類似。從字符串C1種查找C2的內(nèi)容，如果找到的話利用C3來(lái)代替。如果C3 的內(nèi)容為空，則所有在字段中出現(xiàn)C2內(nèi)容的字符都將被刪除。那要實(shí)現(xiàn)上面的需求，就可以把這個(gè)函數(shù)的參數(shù)設(shè)置為Replace(Adress，下沙市，下沙區(qū))。然后再利用Upadate語(yǔ)句把這個(gè)函數(shù)返回的值賦值給地址字段即可。如此，在數(shù)據(jù)庫(kù)地址字段中，出現(xiàn)下沙市的地方將全部被更新為下沙區(qū)。但是這個(gè)更改不會(huì)影響到其他的記錄。如不會(huì)把杭州市也改為杭州區(qū)等等。
為此，要實(shí)現(xiàn)一些復(fù)雜的字符替換作業(yè)，可以利用函數(shù)Replace來(lái)完成。有時(shí)會(huì)，把Replace函數(shù)與其他函數(shù)相結(jié)合，還可以實(shí)現(xiàn)一些更加復(fù)雜的內(nèi)容。如在產(chǎn)品基本信息中，產(chǎn)品編號(hào)中可能包含了產(chǎn)品的分類信息，如PSD001這個(gè)產(chǎn)品編號(hào)，第一個(gè)字符P表示成批，第二三個(gè)字符SD表示手動(dòng)工具。如果自動(dòng)工具的話就為PZD001。在生成報(bào)表的時(shí)候，如果利用這些字符來(lái)顯示結(jié)果的話，顯然報(bào)表的可讀性并不是很好。此時(shí)數(shù)據(jù)庫(kù)管理員可以先利用字符串截取函數(shù)或者利用上面所提到過的頭部填充函數(shù)等等來(lái)截取相關(guān)的字符串，然后再利用現(xiàn)在這個(gè)查找替換函數(shù)，則可以把ZD等字符轉(zhuǎn)換成“自動(dòng)工具”等代表含義的字符串，這將提高報(bào)表的可讀性?？梢?，將Replace替換函數(shù)與其他函數(shù)結(jié)合(C1、C2、C3參數(shù)都可以利用函數(shù)來(lái)實(shí)現(xiàn))從而可以實(shí)現(xiàn)復(fù)雜的替換功能。
【編輯推薦】
