# Let's go inside

this blog is deprecated as a result of laziness.
posts - 59, comments - 2, trackbacks - 0, articles - 0

select REPLACE_NUM('101 North Steet') from dual

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;

if语句块不能用花括号，用if 条件 then 语句块 end if;
for语句FOR i IN 1 .. num，

 只有注册用户登录后才能发表评论。 网站导航: 相关文章: