如何查詢欄位中有 '/' 或其他分割符號的資料

首先說明, 本篇功能只適用於 Oracle 9i 以上

先建立以下的 Collection Type

CREATE TYPE tstrings AS TABLE OF VARCHAR2(1000);

再來建立兩個 Function

一個是分割用的的 function (Split), 這是網路找到的(推!)

CREATE OR REPLACE FUNCTION Split

(

PC$Chaine IN VARCHAR2, -- input string

PN$Pos IN PLS_INTEGER, -- token number

PC$Sep IN VARCHAR2 DEFAULT ',' -- separator character

)

RETURN VARCHAR2

IS

LC$Chaine VARCHAR2(32767) := PC$Sep || PC$Chaine ;

LI$I PLS_INTEGER ;

LI$I2 PLS_INTEGER ;

BEGIN

LI$I := INSTR( LC$Chaine, PC$Sep, 1, PN$Pos ) ;

IF LI$I > 0 THEN

LI$I2 := INSTR( LC$Chaine, PC$Sep, 1, PN$Pos + 1) ;

IF LI$I2 = 0 THEN LI$I2 := LENGTH( LC$Chaine ) + 1 ; END IF ;

RETURN( SUBSTR( LC$Chaine, LI$I+1, LI$I2 - LI$I-1 ) ) ;

ELSE

RETURN NULL ;

END IF ;

END;

另一個 function 則是建立將分割的字串轉成 Collection

CREATE OR REPLACE FUNCTION cosites

( v_cosites varchar2, delimiter varchar2 default '/') RETURN TSTRINGS IS

result TStrings := TStrings();

i integer;

str varchar(100);

BEGIN

i := 1;

loop

str := split(v_cosites, i, delimiter);

exit when str is null;

result.extend(1);

result(i) := str;

i := i + 1;

end loop;

return result;

END;

到這邊工具就準備好了，接著試試看結果囉..

select * from Table(cosites('A100/B100/C100'))

結果就會出現

A100

B100

C100