informix 11.5 charindex

informix 11.5(含)版本以前沒有提供charindex功能.
因為想要依傳入的尋找 字元組合來取得符合的position,所以為了方便,自已寫了一個湊合著用
以下function是區分大小寫尋找的..

--DOP FUNCTION uf_charindex;
/*
in_str:被搜尋的字元
in_delimiter;尋找字元
in_occurence:被找到的次數
in_exact:是否依in_delimiter逐字尋找 

*/

CREATE FUNCTION uf_charindex
(in_str VARCHAR(255),in_delimiter VARCHAR(255)
,in_occurence INTEGER, in_exact CHAR(1) )
RETURNING INTEGER ;

DEFINE q_pos INTEGER;
DEFINE i INTEGER;
DEFINE q_len INTEGER;
DEFINE q_time INTEGER;
DEFINE j INTEGER;
DEFINE q_len_del INTEGER;
DEFINE q_found CHAR(1);
DEFINE q_str VARCHAR(255);
DEFINE q_step INTEGER;

IF nvl(in_occurence,1) <=1 THEN
 LET in_occurence = 1;
END IF;

LET q_time=0;
LET i = 1;
LET q_pos = 0;
LET j = 1;
LET q_found = '0';
LET q_step = 1;
LET q_len = char_length(in_str);
LET q_len_del = char_length(in_delimiter);

--是否依in_delimiter逐字尋找符合字
IF in_exact = '1' THEN --完全符合
    LET q_step = q_len_del;--先記下完整字長度
    LET q_len_del = 1; --最外層只跑一次即可
END IF ;

FOR j = 1 TO q_len_del STEP 1
   LET i = 1;
   LET q_time = 0;
   LET q_pos = 0;
   LET q_found = '0';

   --取比較字(若in_exact='1'則全部符合)
   IF in_exact = '1' THEN
      LET q_str = in_delimiter;
   ELSE --否則逐字找直到找到in_occurence為止
      LET q_str =  substr(in_delimiter,j,1);
   END IF;
   FOR i = 1 TO q_len STEP 1
      IF substr(in_str,i,q_step) = q_str THEN
         LET q_pos = i;
         LET q_time = q_time +1;
         --找到後若與指定出現次數相符則結束
         IF q_time = in_occurence THEN
            LET q_found = '1';
            EXIT FOR;
         END IF;
     END IF;
   END FOR;
   IF q_found = '1' THEN
      EXIT FOR;
   END iF;
END FOR;

RETURN q_pos;

END FUNCTION;



測試一下

SELECT FIRST 1 uf_charindex("臺東縣臺東市","縣市",1,'0') FROM systables;
傳回3

SELECT FIRST 1 uf_charindex("臺北市信義區","縣市",1,'0') FROM systables;
傳回3

SELECT FIRST 1 uf_charindex("臺東縣臺東市","市縣",1,'0') FROM systables;
傳回6

SELECT FIRST 1 uf_charindex("This is a book","book",1,'1') FROM systables;
傳回11

SELECT FIRST 1 uf_charindex("This are a lot of bOOks","O",2,'1') FROM systables;
傳回21