Mysql Get Unique Values from multi value field

Question

I have got a MySQL table with following structure

id       categories
1        ["Pizza","Subs","Sandwiches","Seafood","Italian"]
2        ["Pizza","Salad","Sandwiches","Subs"]
3        ["Fast Food","Burgers","Salad","American","Cafe"]
4        ["Coffee","Cafe"]

I need to get list of all unique category names via SQL query

You may find this useful: stackoverflow.com/questions/1096679/can-mysql-split-a-column — PM 77-1
– PM 77-1, Commented Dec 22, 2013 at 17:32

Gordon Linoff · Accepted Answer · 2013-12-23 00:14:00Z

2

Let's assume you have a maximum of 10 categories. You can get the list using substring_index().

select distinct category
from (select substring_index(substring_index(categories, ',', n.n), ',', -1) as category
      from (select replace(replace(replace(categories, '"', ''), '[', ''), ']', '') as categories
            from t
          ) t cross join
          (select 1 as n union all select 2 union all select 3 union all select 4 union all select 5 union all
           select 6 union all select 7 union all select 8 union all select 9 union all select 10
          ) n
      ) c
where category is not null;

If you have a longer list, just add more values to the subquery for n. The complex replace() assumes that you don't actually want the double quotes and square brackets and that they are not really needed to distinguish among the categories.

As noted in the comments, this is a very bad data structure. You should have a table for item_categories with one row for an item and a single category.

edited Dec 23, 2013 at 0:14

answered Dec 22, 2013 at 17:36

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Tin Tran Over a year ago

i think you're missing an extra substring_index call i edited for you. proof here sqlfiddle.com/#!2/f8d3d/3/0. Nice answer btw!

Ayaz Alavi Over a year ago

Thanks a ton dude, let me find out result.

Tin Tran Over a year ago

@AyazAlavi, you might want to add AND category != '' at the end if you have rows that might not have any category like []

Collectives™ on Stack Overflow

Mysql Get Unique Values from multi value field

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related