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

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)

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

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