-2

Currently I have this table yeast_module which has this field beer_style

Which currently, every single row in the yeast_module has this beer_style data

American Amber,American Brown Ale,American India Pale Ale,American Pale Ale,American Porter,American Stout,Barleywine Ale,Belgian Dark Strong Ale,Belgian Table Bier,Belgian Wit,Blonde Ale,Cream Ale,Dusseldorf Altbier,English Brown Ale,English India Pale A

Now, I wondering if I can split it and select it distinctly.

I'm playing around on this command

SELECT DISTINCT beer_style FROM yeast_module

My expected output is

  American Amber,
  American Brown Ale,
  So on..

Here's the sample data I have enter image description here

GMB
  • 216,147
  • 25
  • 84
  • 135
Pablo
  • 1,357
  • 1
  • 11
  • 40

1 Answers1

2

The way you store your data makes this kind of task tedious (and inherently inefficient). Storing csv lists in a table column is a typical SQL antipattern, that should almost always be avoided.

Instead, you should have a separate table to represent the one-to-many relationship between yeast modules and beer styles, with each tuple of values on a separate row.

If you are running MySQL 8.0, you can use a recursive query to unnest the string to rows:

with recursive cte as (
    select 
        substring(beer_style, 1, locate(',', concat(beer_style, ',')) - 1) parsed,
        substring(concat(beer_style, ','), locate(',', concat(beer_style, ',')) + 1) remaining
    from mytable
    union all
    select 
        substring(remaining, 1, locate(',', remaining) - 1),
        substring(remaining, locate(',', remaining) + 1)
    from cte
    where locate(',', remaining) > 0
)
select parsed from cte

Recommended reading: Is storing a delimited list in a database column really that bad?

GMB
  • 216,147
  • 25
  • 84
  • 135
  • As of now, I'm trying to get it as distinct so I can get all the list of `beer_style` and make a table for list of `beer_style` to have its own primary key and assign it on `yeast_module` accordingly to make the query easier thanks for your answer – Pablo Aug 27 '20 at 23:10
  • @RaeIan: just the same query, with `select distinct parsed from cte`. – GMB Aug 27 '20 at 23:13