CODEWITHSUNDEEP
mysql
user3833734
user3833734

Reputation: 11

MYSQL - Count items separated by comma or not

I have a tinytext field which can contain 3 differents value formatted as followed:

I want to query the table and count the number of items separated or not with a comma.

For example with these lines:

Then the expected count would be 6.

I can't find the correct query.

Upvotes: 1

Views: 1671

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562398

Okay here's the test data:

mysql> create table t (f tinytext);
mysql> insert into t values ('42'), (null), ('42,56,99'), ('24,10090');
mysql> select * from t;
+----------+
| f        |
+----------+
| 42       |
| NULL     |
| 42,56,99 |
| 24,10090 |
+----------+

You can calculate how many numbers in the string as the difference in the length of the string and the string with commas removed (add 1 for the first number in the list).

mysql> select f, length(f), length(replace(f,',','')), 1+ length(f)-length(replace(f,',','')) from t;
+----------+-----------+---------------------------+----------------------------------------+
| f        | length(f) | length(replace(f,',','')) | 1+ length(f)-length(replace(f,',','')) |
+----------+-----------+---------------------------+----------------------------------------+
| 42       |         2 |                         2 |                                      1 |
| NULL     |      NULL |                      NULL |                                   NULL |
| 42,56,99 |         8 |                         6 |                                      3 |
| 24,10090 |         8 |                         7 |                                      2 |
+----------+-----------+---------------------------+----------------------------------------+

So then use SUM() to get the total. SUM() ignores NULLs.

mysql> select sum(1+length(f)-length(replace(f,',',''))) from t;
+--------------------------------------------+
| sum(1+length(f)-length(replace(f,',',''))) |
+--------------------------------------------+
|                                          6 |
+--------------------------------------------+

This would be easier if you don't store comma-separated lists in a string.

Upvotes: 2

Related Questions