При использовании представления V как источника данных, подвергающихся впоследствии синтаксическому разбору, решение выглядит следующим образом:

1 with cartesian as (

2 select level id

3 from dual

4 connect by level <= 100

6 select max(decode(id,1,substr(strings,p1+1,p2-1))) val1,

7 max(decode(id,2,substr(strings,p1+1,p2-1))) val2,

8 max(decode(id,3,substr(strings,p1+1,p2-1))) val3

9 from (

10 select v.strings,

11 c.id,

12 instr(v.strings,:,1,c.id) p1,

13 instr(v.strings,:,1,c.id+1)-instr(v.strings,:,1 ,c.id) p2

14 from v, cartesian c

15 where c.id <= (length(v.strings)-length(replace(v.strings,:)))-1

16 )

17 group by strings

18 order by 1

Обсуждение

Первый шаг - обход сериализованных строк:

здесь ничего больше не требуется, очень просто трансформировать решение под другие платформы:

with cartesian as ( select level id from dual connect by level <= 100

select v.strings, c.id

from v,cartesian c where c.id <= (length(v.strings)-length(replace(v.strings,:)))-1

STRINGS ID

entry:::flanders: 1

entry:::flanders: 2

entry:::flanders: 3

entry:moe::sizlack: 1

entry:moe::sizlack: 2

entry:moe::sizlack: 3

entry:petergriffin:meg:chris: 1

entry:petergriffin:meg:chris: 3

entry:petergriffin:meg:chris: 2

entry:quagmire:mayorwest:cleveland: 1

entry:quagmire:mayorwest:cleveland: 3

entry:quagmire:mayorwest:cleveland: 2

entry:robo:tchi:ken: 1

entry:robo:tchi:ken: 2

entry:robo:tchi:ken: 3

entry:stewiegriffin:lois:brian: 1

entry:stewiegriffin:lois:brian: 3

entry:stewiegriffin:lois:brian: 2

entry:willie: 1

Следующий шаг - используя функцию INSTR, находим числовую позицию каждого двоеточия в каждой строке. Поскольку все значения, которые должны быть извлечены, заключены в два двоеточия, числовым значениям присвоены псевдонимы P1 и P2, что означет position 1 и position 2 соответственно:

with cartesian as ( select level id from dual

connect by level <= 100

select v.strings, c.id,

instr(v.strings,:,1,c.id) p1,

instr(v.strings,:,1,c.id+1)-instr(v.strings,:,1,c.id) p2 from v,cartesian c where c.id <= (length(v.strings)-length(replace(v.strings,:)))-1 order by 1

STRINGS ID P1 P2

Теперь, когда нам известны числовые позиции каждой пары двоеточий в каждой строке, просто передаем эту информацию в функцию SUBSTR для извлечения значений. Поскольку должно быть создано результирующее множество с тремя столбцами, используем функцию DECODE, чтобы вычислить ID из декартова произведения:

with cartesian as ( select level id from dual

connect by level <= 100

select decode(id,1,substr(strings,p1+1,p2-1)) val1,

decode(id,2,substr(strings,p1+1,p2-1)) val2,

decode(id,3,substr(strings,p1+1,p2-1)) val3 from ( select v.strings,

c.id,

instr(v.strings,:,1,c.id) p1,

instr(v.strings,:,1,c.id+1)-instr(v.strings,:,1,c.id) p2 from v,cartesian c where c.id <= (length(v.strings)-length(replace(v.strings,:)))-1 )

order by 1

VAL1 VAL2 VAL3

petergriffin

quagmire

robo

stewiegriffin willie