因?yàn)閛racle9i不支持正則表達(dá)式，REPLACE函數(shù)不夠強(qiáng)大，只好自己寫了一個(gè)函數(shù)。
需求：
把給定的字符串中的數(shù)字部分去掉，如果101 North Steet替換成 North Street
使用方法:
select REPLACE_NUM('101 North Steet') from dual
開發(fā)工具: Toad(有Debug功能，寫function, procedure非常方便)
方法。
1。先找到Toad中Schema Browser的Procs標(biāo)簽欄。
2。Create New Object. Object Type: function. New Object Name: REPLACE_NUM. OK!
3。代碼如下：
/* Formatted on 2006/05/15 13:57 (Formatter Plus v4.8.5) */
CREATE OR REPLACE FUNCTION replace_num (street_name IN VARCHAR2)
?? RETURN VARCHAR2
IS
?? RESULT?????? VARCHAR2 (1000);
?? num????????? NUMBER (3)????? := 0;
?? beginindex?? NUMBER (3)????? := 1;
?? endindex???? NUMBER (3)????? := 1;
?? j??????????? NUMBER (3)????? := 1;
?? letter?????? CHAR (1);
?? temp???????? CHAR (1);
BEGIN
?? num := LENGTH (street_name);
?? IF num IS NULL OR num = 0
?? THEN
????? RETURN street_name;
?? END IF;
?? endindex := num;
?? -- find the first digit
?? FOR i IN 1 .. num
?? LOOP
????? temp := SUBSTR (street_name, i, 1);
????? IF (ASCII (temp) >= 48 AND ASCII (temp) <= 57)
????? THEN
???????? beginindex := i;
???????? EXIT;
????? END IF;
?? END LOOP;
?? -- find last digit
?? j := num;
?? WHILE j >= 1
?? LOOP
????? temp := SUBSTR (street_name, j, 1);
????? IF (ASCII (temp) >= 48 AND ASCII (temp) <= 57)
????? THEN
???????? endindex := j;
???????? EXIT;
????? END IF;
????? j := j - 1;
?? END LOOP;
?? -- find digit block
?? RESULT := SUBSTR (street_name, beginindex, endindex - beginindex + 1);
?? -- return string without number
?? RESULT := REPLACE (street_name, RESULT);
?? RETURN RESULT;
END replace_num;
第一次寫函數(shù)，碰到了下面一些問題:
聲明變量的時(shí)候用DECLARE會(huì)報(bào)錯(cuò)，后來(lái)改成IS就好了
賦值符號(hào)是:=,不是=， 條件與是and不是&&
if語(yǔ)句塊不能用花括號(hào)，用if 條件 then 語(yǔ)句塊 end if;
for語(yǔ)句FOR i IN 1 .. num，
其中1..num不能寫成num..1，我以為這樣可以使i遞減，原來(lái)不可以
后來(lái)改成while語(yǔ)句，并用j := j - 1;進(jìn)行遞減
跳出循環(huán)是exit不是break, LENGTH(null)返回null而不是0
就碰到了這些問題，記下來(lái)備用。