[SQL]一個簡單又複雜的 split string sql

為解決在 sql 中 split string 的需求, 找到了:
http://www.oracle.com/technology/oramag/code/tips2007/070907.html

SQL 是我最大的罩門, 只會最基本的 SQL statement, 尤其在用了 django 後, 根本連寫的機會都快沒了. 這一篇裡有很多函數根本沒聽過...看得辛苦.

Data prepare:

create table tab1 (owner number, cars varchar2(200));
insert into tab1 (
  select 1, 'Ford,Toyota,Nissan' from dual union all
  select 2, 'Lexus,Mercedes,BMW,Infiniti' from dual union all
  select 3, 'Ferrari' from dual union all
  select 4, 'Porsche,Lotus,Lamborghini,Maserati,Aston Martin' from dual union all
  select 5, 'Maybach,Bentley' from dual);

Split demo:

select owner, car
from
(
   select owner ,
        trim(substr(str, instr(str, ',', 1, level) + 1, instr(str, ',', 1, level + 1) - instr(str, ',', 1, level) - 1)) car ,
        level lv ,
        lag(level, 1, 0) over (partition by owner order by level) lg
   from
   (
      select owner, ','||cars||',' str from bda_din_tab1
   )
   connect by instr(str, ',', 1, level) > 0
)
where car is not null and lv != lg;