CODEWITHSUNDEEP
mysqlsql
Tomas Reimers
Tomas Reimers

Reputation: 3292

How to count items in comma separated list MySQL

So my question is pretty simple:

I have a column in SQL which is a comma separated list (ie cats,dogs,cows,) I need to count the number of items in it using only sql (so whatever my function is (lets call it fx for now) would work like this:

 SELECT fx(fooCommaDelimColumn) AS listCount FROM table WHERE id=...

I know that that is flawed, but you get the idea (BTW if the value of fooCommaDelimColumn is cats,dogs,cows,, then listCount should return 4...).

That is all.

Upvotes: 41

Views: 76607

Answers (6)

TRIKONINFOSYSTEMS
TRIKONINFOSYSTEMS

Reputation: 640

If we do +1 and if we have an empty column it always comes as 1 to make it 0 we can use IF condition in mySQL.

IF(LENGTH(column_name) > 0, LENGTH(column_name) - LENGTH(REPLACE(column_name, ',', '')) + 1, 0)

Upvotes: 1

zerkms
zerkms

Reputation: 254944

There is no built-in function that counts occurences of substring in a string, but you can calculate the difference between the original string, and the same string without commas:

LENGTH(fooCommaDelimColumn) - LENGTH(REPLACE(fooCommaDelimColumn, ',', ''))

It was edited multiple times over the course of almost 8 years now (wow!), so for sake of clarity: the query above does not need a + 1, because OPs data has an extra trailing comma.

While indeed, in general case for the string that looks like this: foo,bar,baz the correct expression would be

LENGTH(col) - LENGTH(REPLACE(col, ',', '')) + 1

Upvotes: 94

arlomedia
arlomedia

Reputation: 9061

This version doesn't support leading or trailing commas, but supports an empty value with a count of 0:

IF(values, LENGTH(values) - LENGTH(REPLACE(values, ',', '')) + 1, 0) AS values_count

Upvotes: 3

tauanz
tauanz

Reputation: 291

Following the suggestion from @zerkms.

If you dont know if there is a trailing comma or not, use the TRIM function to remove any trailing commas:

(
    LENGTH(TRIM(BOTH ',' FROM fooCommaDelimColumn))
  - LENGTH(REPLACE(TRIM(BOTH ',' FROM fooCommaDelimColumn), ',', ''))
  + 1
) as count

Reference: http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_trim

I also agree that a refactoring of the tables is the best option, but if this is not possible now, this snippet can do the work.

Upvotes: 7

Vincent Savard
Vincent Savard

Reputation: 35927

zerkms' solution works, no doubt about that. But your problem is created by an incorrect database schema, as Steve Wellens pointed out. You should not have more than one value in one column because it breaks the first normal law. Instead, you should make at least two tables. For instance, let's say that you have members who own animals :

table member (member_id, member_name)
table member_animal (member_id, animal_name)

Even better: since many users can have the same type of animal, you should create 3 tables :

table member (member_id, member_name)
table animal (animal_id, animal_name)
table member_animal (member_id, animal_id)

You could populate your tables like this, for instance :

member (1, 'Tomas')
member (2, 'Vincent')
animal (1, 'cat')
animal (2, 'dog')
animal (3, 'turtle')
member_animal (1, 1)
member_animal (1, 3)
member_animal (2, 2)
member_animal (2, 3)

And, to answer your initial question, this is what you would do if you wanted to know how many animals each user has :

SELECT member_id, COUNT(*) AS num_animals
FROM member
INNER JOIN member_animal
    USING (member_id)
INNER JOIN animal
    USING (animal_id)
GROUP BY member_id;

Upvotes: 8

Steve Wellens
Steve Wellens

Reputation: 20620

The answer is to correct the database schema. It sounds like a many-to-many relationship which requires a junction table. http://en.wikipedia.org/wiki/Junction_table

Upvotes: 1

Related Questions