1)instr()函數的格式? (俗稱:字符查找函數)
格式壹:instr( string1, string2 ) ? /? instr(源字符串, 目標字符串)
格式二:instr( string1, string2 [, start_position [, nth_appearance ] ] ) ? /? instr(源字符串, 目標字符串, 起始位置, 匹配序號)
解析:string2 的值要在string1中查找,是從start_position給出的數值(即:位置,-1表示從尾部開始倒數第1個)開始在string1檢索,檢索第nth_appearance(幾)次出現string2。
SELECT?A2.id,A1.name?from?A2?left?join
A1?on?A2.codeall=A1.code
where?LENGTHB(codeall)-LENGTHB(replace(codeall,',',''))?=?0
union?all
SELECT?A2.id,A11.name||','||A12.name?
from?A2?left?join
A1?A11?on?substr(A2.codeall,1,instr(A2.codeall,',')-1)=A11.code?left?join
A1?A12?on?substr(A2.codeall,instr(A2.codeall,',')+1,instr(A2.codeall,',',-1,1)-1)=A12.code
where?LENGTHB(codeall)-LENGTHB(replace(codeall,',',''))?=?1
union?all
SELECT?A2.id,A11.name||','||A12.name?||','||A13.name?
from?A2?left?join
A1?A11?on?substr(A2.codeall,1,instr(A2.codeall,',')-1)=A11.code?left?join
A1?A12?on?substr(A2.codeall,instr(A2.codeall,',')+1,instr(A2.codeall,',',1,2)-1)=A12.code
A1?A13?on?substr(A2.codeall,instr(A2.codeall,',',1,2)+1,instr(A2.codeall,',',-1,1)-1)=A13.code
where?LENGTHB(codeall)-LENGTHB(replace(codeall,',',''))?=?2
union?all
SELECT?A2.id,A11.name||','||A12.name?||','||A13.name?||','||A14.name?
from?A2?left?join
A1?A11?on?substr(A2.codeall,1,instr(A2.codeall,',')-1)=A11.code?left?join
A1?A12?on?substr(A2.codeall,instr(A2.codeall,',')+1,instr(A2.codeall,',',1,2)-1)=A12.code
A1?A13?on?substr(A2.codeall,instr(A2.codeall,',',1,2)+1,instr(A2.codeall,',',1,3)-1)=A13.code
A1?A14?on?substr(A2.codeall,instr(A2.codeall,',',1,3)+1,instr(A2.codeall,',',-1,1)-1)=A14.code
where?LENGTHB(codeall)-LENGTHB(replace(codeall,',',''))?=?3