Let's go inside

因為oracle9i不支持正則表達式，REPLACE函數不夠強大，只好自己寫了一個函數。

需求：
把給定的字符串中的數字部分去掉，如果101 North Steet替換成 North Street

使用方法:
select REPLACE_NUM('101 North Steet') from dual

開發工具:　Toad(有Debug功能，寫function, procedure非常方便)

方法。

1。先找到Toad中Schema Browser的Procs標簽欄。
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;
第一次寫函數，碰到了下面一些問題:
聲明變量的時候用DECLARE會報錯，后來改成IS就好了
賦值符號是:=,不是=，　條件與是and不是&&
if語句塊不能用花括號，用if 條件 then 語句塊 end if;
for語句FOR i IN 1 .. num，　
其中1..num不能寫成num..1，我以為這樣可以使i遞減，原來不可以
后來改成while語句，并用j := j - 1;進行遞減
跳出循環是exit不是break, LENGTH(null)返回null而不是0
就碰到了這些問題，記下來備用。