Oracle網站上的每周一帖(Tip for Week of December 13, 2004)

計算一個字符串在另外一個字符串裡出現的次數

Aui de la Vega,菲律賓

CREATE FUNCTION NUM_CHARS(INSTRING VARCHAR2, INPATTERN VARCHAR2)

RETURN NUMBER

IS

COUNTER NUMBER;

NEXT_INDEX NUMBER;

STRING VARCHAR2(2000);

PATTERN VARCHAR2(2000);

BEGIN

COUNTER := 0;

NEXT_INDEX := 1;

STRING := LOWER(INSTRING);

PATTERN := LOWER(INPATTERN);

FOR I IN 1 .. LENGTH(STRING) LOOP

IF (LENGTH(PATTERN) <= LENGTH(STRING)-NEXT_INDEX+1)

AND (SUBSTR(STRING,NEXT_INDEX,LENGTH(PATTERN)) = PATTERN) THEN

COUNTER := COUNTER+1;

END IF;

NEXT_INDEX := NEXT_INDEX+1;

END LOOP;

RETURN COUNTER;

END;

代碼分析：

該代碼不是很復雜，利用NEXT_INDEX加一遞增，對INSTRING做遍歷，從INSTRING字符串的第NEXT_INDEX個字符開始截取

LENGTH(PATTERN)長度的字符串，如果截取的字符串等於PATTERN,則計數器COUNTER+1,在PL/SQL Developer+Oracle10+WINNT

調試成功。