[Script] Split to Rows

 http://stackoverflow.com/questions/17942508/sql-split-values-to-multiple-rows

numbers 라는 테이블에 약 8,000개 정도의 일련번호 값을 넣어 둡니다. (MSSQL 과 비슷한 방식)

I have table :

id | name   

1  | a,b,c   

2  | b

i want output like this :

id | name   

1  | a   

1  | b   

1  | c   

2  |

If you can create a numbers table, that contains numbers from 1 to the maximum fields to split, you could use a solution like this:

select

  tablename.id,

  SUBSTRING_INDEX(SUBSTRING_INDEX(tablename.name, ',', numbers.n), ',', -1) name

from

  numbers inner join tablename

  on CHAR_LENGTH(tablename.name)

     -CHAR_LENGTH(REPLACE(tablename.name, ',', ''))>=numbers.n-1

order by

  id, n

Please see fiddle here.

If you cannot create a table, then a solution can be this:

select

  tablename.id,

  SUBSTRING_INDEX(SUBSTRING_INDEX(tablename.name, ',', numbers.n), ',', -1) name

from

  (select 1 n union all

   select 2 union all select 3 union all

   select 4 union all select 5) numbers INNER JOIN tablename

  on CHAR_LENGTH(tablename.name)

     -CHAR_LENGTH(REPLACE(tablename.name, ',', ''))>=numbers.n-1

order by

  id, n

an example fiddle is here.