oracle字段值批量替換

1）instr()函數的格式? （俗稱：字符查找函數）

格式壹：instr( string1, string2 ) ? /? instr(源字符串, 目標字符串)

格式二：instr( string1, string2 [, start_position [, nth_appearance ] ] ) ? /? instr(源字符串, 目標字符串, 起始位置, 匹配序號)

解析：string2 的值要在string1中查找，是從start_position給出的數值（即：位置，-1表示從尾部開始倒數第1個）開始在string1檢索，檢索第nth_appearance（幾）次出現string2。

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