SQL query to retrieve values from a comma separated column

Question

Possible Duplicate:
Split string in SQL
Searching a column with comma seperated values

In my PHP project I'm facing a problem to write a SQL:

I have a table "consultants" with fields categories which has values like:

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
+ id |  consultant_name  | categories                           +
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
+  1 |  AOAOAO           | health,insurance,programming,        +
+  2 |  BOBOBO           | health,gaming,windows,mobile,        +
+  3 |  CCCCCC           | insurance,windows,                   + 
+  4 |  DDDDDD           | mobile,                              +
+  . |  ......           | ............                         +
+  . |  ......           | ............                         +
+  . |  ......           | ............                         +
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

And I have an array which contains all the categories that a consultant can register with.

$arrayOfCategories =  $array("health","insurance","programming",
                             "sports","gaming","windows","apple",
                             "mobile","astrology");

What I want is a SQL Query that should give me an output like:

+++++++++++++++++++++++++++++++
+ category      | occurrence  +    
+++++++++++++++++++++++++++++++
+ health        | 2           +
+ insurance     | 2           +
+ programming   | 1           +
+ sports        | 0           +
+ gaming        | 1           +
+ windows       | 2           +
+ apple         | 0           +
+ mobile        | 2           +
+ astrology     | 0           +
+++++++++++++++++++++++++++++++

Any kind of help will be appreciated...

Thanks In Advance...

@ShaktiSingh : Yes I agree with you. But what to do if changes were asked at the last time. — , Jan 07 '12 at 13:28
You're looking for the opposite of [`GROUP_CONCAT()`](http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat). — hakre, Jan 07 '12 at 13:30
Use http://stackoverflow.com/questions/8742093/split-string-into-multiple-rows-in-sql to transform your DB schema into something sane — Eugen Rieck, Jan 07 '12 at 13:32
You really should consider normalizing your consultants table. With this kind of table you'll always be in trouble! — Mithrandir, Jan 07 '12 at 13:39

score 1 · Answer 1 · answered Jan 07 '12 at 13:39

This is a bad database design. If I were stuck with it, I wouldn't try to use SQL to get the occurrences, but my application.

Create a new array called $occurence, this will have an index of each word and a count. Select every row and split the contents of categories on the comma. Loop through this resulting array and increment for every word.

I'm a little rusty in PHP so here is my suggestion in Perl:

my %occurrence;

while ( my ($categories) = $sth->fetchrow_array() ){

   my @cats = split(',', $categories);

   foreach my $c (@cats){
      $occurrence{$c}++;
   }

}

One of the benefits of this way is you will find any mispelled categories and don't have to update your SQL when a new category gets added.

score 1 · Accepted Answer · answered Jan 07 '12 at 14:29

Bad database design but interesting question, here it is my way to do (php code):

$arrayOfCategories =  $array("health","insurance","programming",
                             "sports","gaming","windows","apple",
                             "mobile","astrology");

 $count = count($arrayOfCategories);
 $query = "";
 for($i = 0; $i < $count; $i++)
 {
   $value = $arrayOfCategories[$i];
   $query += "SELECT '$value' AS category,
                 COUNT(*) AS occurrence
                 FROM consultants
                 WHERE FIND_IN_SET('$value', categories)";
    if($i < $count -1) {
    $query += " UNION "
    }


 }

The result of the generated query should give you exactly what you want . Often from a bad design comes interesting questions ... :)

Thanks dude, it is working perfectly as I wanted... :) – Jan 07 '12 at 14:53 — , Jan 07 '12 at 14:53

score 0 · Answer 3 · answered Jan 07 '12 at 13:39

0

If you had a table with categories, you could do it like this:

SELECT cat.name, COUNT(*) 
FROM categories cat JOIN consultants con ON con.categories LIKE '%'+cat.name+'%'

Still, I consider this awkward and you should consider normalising the db scheme so that there's a mapping table for the consultant-category relation

answered Jan 07 '12 at 13:39

voidengine

2,504
1
17
29

The query would of course fail if one category is a substring of the other – voidengine Jan 07 '12 at 13:42
Not if it were `like '%,'+cat.name+',%'` and you ensure that `con.categories` had a comma at either end. – Ben Jan 07 '12 at 13:52
@Ben I know, I just don't think it's worth bothering as this is not a clever thing to do anyway... – voidengine Jan 07 '12 at 13:57
I'd agree with you there! Spending the time to normalise the schema would save a lot more time in the long run – Ben Jan 07 '12 at 13:59

SQL query to retrieve values from a comma separated column

3 Answers3