Question

Splitting Strings into Rows in the Absence of Table-Generating Functions

  • 15 November 2015
  • 6 replies
  • 732 views

  • Anonymous
  • 0 replies


This is excellent Scott! Curious if you or anyone in the community has developed a pattern towards un-nesting a JSON array into rows. Thanks!


6 replies

For those using MySQL, here’s a similar solution



instead of split_part(products.tags, ', ', numbers.num) as tag_name


use: SUBSTRING_INDEX(SUBSTRING_INDEX(products.tags, ', ', n.number + 1), ', ', -1) as tag_name



instead of regexp_count(products.tags, ‘,\s’) + 1


you can use:


${numbers.SQL_TABLE_NAME} as n ON n.number < (ROUND((LENGTH(products.tags) - LENGTH(REPLACE(products.tags, ", “, “”))/LENGTH(”, ")) + 1)

i am having table forms


in that table forms table formnames stored in form1,form2,form3 like this


how to split this formNAMES into rows display in another table.(in mysql)



please let me solution asap



Thanks & regards,


shiva

Userlevel 2

Hey @shivankgurram,



I borrowed from this resource to come up with a mysql solution:



SELECT

DISTINCT SUBSTRING_INDEX(SUBSTRING_INDEX(vals, ',', n.digit+1), ',', -1) val

FROM

(select "form1,form2,form3" as vals) tt1

INNER JOIN

(SELECT 0 digit UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6) n

ON LENGTH(REPLACE(vals, ',' , '')) <= LENGTH(vals)-n.digit;





I hope that helps!



Thanks,


Philip M

hi



thanks for reply


my requirement is same for above "products " table,


but i want query in mysql



Thanks,


siva

Userlevel 2

Hey @shivankgurram,



The sample query above is mysql and should both work in your db and manage a similarly structured table as the one provided in the article with a few modifications.



Take this table:





By selecting the num field alongside the vals field, you get this:



select @rownum:=@rownum+1 row_num,a.*

FROM(



SELECT

distinct num, SUBSTRING_INDEX(SUBSTRING_INDEX(vals, ',', n.digit+1), ',', -1) val

FROM

(select 123 as num,"form1,form2,form3" as vals

UNION

select 234 as num,"form4,form5,form6" as vals

) tt1

INNER JOIN

(SELECT 0 digit UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6) n

ON LENGTH(REPLACE(vals, ',' , '')) <= LENGTH(vals)-n.digit

ORDER BY 1,2

)a,(SELECT @rownum:=0)b;





Thanks,


Philip M

thank you philip

Reply