CODEWITHSUNDEEP
phpmysql
user1094081
user1094081

Reputation:

How to find number of occurences of string in comma separated values field

I have a field that contains comma separated values. I found I can verify the presence in the list of "b" using this code:

SELECT FIND_IN_SET('b','a,b,c,d,b,b')

I want a different thing:

I want to find the number of occurrences of 'b' in that comma separated list. Is this possible in MySQL? Or I must demand it to array_count_values in PHP?

Upvotes: 2

Views: 937

Answers (2)

KOGI
KOGI

Reputation: 3989

This blog post seems to do what you want:

http://nostylesheets.com/2009/07/17/mysql-substr-count/

Basically, it looks at the string length of the field, removes your target sub-string, then looks at the new length. If you know your substring was 4 characters long, and your new string is now 8 characters shorter than it was, then you know you had 2 occurrences.

Upvotes: 1

Fletch
Fletch

Reputation: 963

LOCATE should do the trick. Note that there are two different signatures - the first (two args) is to be called initially, then call the second (three args), giving the result of the first, as the third argument to the second. More info at:

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

LOCATE(substr,str), LOCATE(substr,str,pos)

The first syntax returns the position of the first occurrence of substring substr in string str. The second syntax returns the position of the first occurrence of substring substr in string str, starting at position pos. Returns 0 if substr is not in str.

Upvotes: 0

Related Questions