MySQL: How can I return the array length of concatenated data stored in a column with MySQL?

Question

I have data stored in a column called supervisors_id in a table. The structure of data in this column is like this: 12, 44, 55, 32, 85, 75, 45. So I want to fetch the total number of ID's in that supervisors_id column in a row. My expected result, for instance in the above example should be 7. How can I do that with a MySQL query ?

what is the column type of supervisors_id? did you try anything yet to solve this? — Imbar M.
– Imbar M., Commented Sep 9, 2018 at 12:54

sticky bit · Accepted Answer · 2018-09-09 13:24:16Z

2

You can count the commas by comparing the length of the string to the length of the string with commas replaced by the empty string.

Somewhat like:

SELECT length(supervisors_id) - length(replace(supervisors_id, ',', '')) + 1
       FROM elbat;

If there are also empty strings in the column you might need to add some more logic, that checks for that and returns 0 instead of 1 in that case. For example by using a CASE:

SELECT CASE
         WHEN supervisors_id <> '' THEN
           length(supervisors_id) - length(replace(supervisors_id, ',', '')) + 1
         ELSE
           0
       END
       FROM elbat;

But your design is not the best. Instead of a comma delimited list, there should be a linking table.

edited Sep 9, 2018 at 13:24

answered Sep 9, 2018 at 13:06

sticky bit

37.7k12 gold badges34 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Philip Onyango Over a year ago

Thanks man, it worked like magic!! Could you just give me a hint of how using it with a linking table would work. I'm just trying to avoid very long rows in a table, but i'm open to new ideas.

sticky bit Over a year ago

@PhilipOnyango: Welcome! Have a new table, that stores a (as in one) supervisor ID per row and the ID of a row of that other table. I cannot go into more detail as you haven't revealed that much of your schema.

Philip Onyango Over a year ago

I already have a parent table for all the supervisors. So in this table called daily_tasks, I'm simply concatenating a supervisor's ID whenever a supervisor is assigned a task on that day.

sticky bit Over a year ago

@PhilipOnyango: Instead of doing that create another table and insert the supervisors ID and the task ID into it.

Philip Onyango Over a year ago

I've now fully gotten your point. I think it's a better implementation of my situation. Muchas gracias, I appreciate.

Collectives™ on Stack Overflow

MySQL: How can I return the array length of concatenated data stored in a column with MySQL?

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related