1

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

  • NULL
  • "X" or "Y" (where X and Y can be any number)
  • "A,B,C,D" (where A, B, C and D can be any number)

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

For example with these lines:

  • 42
  • NULL
  • 42,56,99
  • 24,10090

Then the expected count would be 6.

I can't find the correct query.

user3833734
  • 11
  • 4
  • There is no query with this function. You will have to write a procedure to split the text and count occurrences. – Flávio Filho Jun 29 '17 at 18:41
  • If you use a database, you never store the data you work with separated by commas. That's like buying a car and then pushing it down the road instead of driving it, thinking it's an excellent use of the tool. Kill that design with fire, store data in ROWS, do not stick them in columns separated by a comma. Databases aren't text files, no need to use them as such. – N.B. Jun 29 '17 at 21:41
  • Thanks for the comments... both of you. I will try to find another way to do it. – user3833734 Jun 30 '17 at 08:50

1 Answers1

2

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.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828