oracle 에서 split 하는 쿼리를 흉내내봤음.
잘 안됨.-_-
결국 , 로 구분된 문자열의 max가 5개 라는 하드코딩을 하게 됨.ㅠㅠ
쿼리는 다음과 같음.
with mas AS (
select name
, len
, case when len >= 1 then
substr(name, 1, instr(name, ',', 1, len1) - 1)
else
''
end a1
, case when len >= 2 then
substr(name, instr(name, ',', 1, len1) + 1, decode(instr(name, ',', 1, len2), 0, 100, instr(name, ',', 1, len2)) - instr(name, ',', 1, len1) - 1)
else
''
end a2
, case when len >= 3 then
substr(name, instr(name, ',', 1, len2) + 1, decode(instr(name, ',', 1, len3), 0, 100, instr(name, ',', 1, len3)) - instr(name, ',', 1, len2) - 1)
else
''
end a3
, case when len >= 4 then
substr(name, instr(name, ',', 1, len3) + 1, decode(instr(name, ',', 1, len4), 0, 100, instr(name, ',', 1, len4)) - instr(name, ',', 1, len3) - 1)
else
''
end a4
, case when len >= 5 then
substr(name, instr(name, ',', 1, len4) + 1, decode(instr(name, ',', 1, len5), 0, 100, instr(name, ',', 1, len5)) - instr(name, ',', 1, len4) - 1)
else
''
end a5
from (
select name
,length(regexp_replace(name, '[^,]', '')) + 1 as len
, len1, len2, len3, len4, len5
-- , instr(name, ',', 1, level)
from (
/* ===== master 테이블 들어가는 곳 ===== */
select 'lee,222,333' as name from dual
union all
select 'lee2,444,555,777' as name from dual
union all
select 'lee3,888,999,000' as name from dual
union all
select '이승구,이승규,승구,승규, babolsk' as name from dual
) a
, (
select 1 as len1
, 2 as len2
, 3 as len3
, 4 as len4
, 5 as len5
from dual
) b
)
)
select keyword from (
select a1 as keyword from mas
union all
select a2 as keyword from mas
union all
select a3 as keyword from mas
union all
select a4 as keyword from mas
union all
select a5 as keyword from mas
) where keyword is not null