Advanced Search

mysql unroll variable length string list

/ Published in: SQL
Save to your folder(s)
unroll variable length string list to normalised form.

Expand | Embed | Plain Text

Copy this code and paste it in your HTML 
  1. -- unrolls variable length list data to normalised form
  2. -- add another cross_join if number of elements in listdata >= 100..
  3. SELECT t.id, substring_index(substring_index(t.list, ',', seq.n), ',', -1) element
  4. FROM variable_length_listdata t 
  5. CROSS JOIN
  6. (
  7.   SELECT a1.n * 10 + a0.n + 1 n
  8.   FROM digits_v a1
  9.   CROSS JOIN digits_v a0
  10.   ORDER BY n
  11. ) seq
  12. WHERE seq.n <= 1 + (LENGTH(t.list) - LENGTH(REPLACE(t.list, ',', '')))
  13. ORDER BY t.id, element
  14. ;

URL: http://sqlfiddle.com/#!9/d1201/4

Report this snippet


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.