Mysql - count values from comma-separated field

Question

I have to do some statics from read-only db where value are stored in a weird form

example: I have 2 rows like

ID    text field 
1     1001,1003,1004 
2     1003, 1005

I need to be able to count that this is "5".

I don't have write access so don't know how to read and count right away without creation a function or something like that.

take a look at my [question](http://stackoverflow.com/questions/14184529/how-to-convert-comma-seperated-column-into-rows-and-add-counter). There is a satisfying answer — Muhammad Raheel, Jan 31 '13 at 20:37

score 33 · Accepted Answer · edited May 23 '17 at 12:26

33

Clever solution here on SO: How to count items in comma separated list MySQL

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

EDIT

Yes you can select it as an additional column: and correcting with the CHAR_LENGTH from @HamletHakobyan's answer:

SELECT 
  ID, 
  textfield, 
  (CHAR_LENGTH(textfield) - CHAR_LENGTH(REPLACE(textfield, ',', '')) + 1) as total 
FROM table

edited May 23 '17 at 12:26

Community

1
1

answered Jan 31 '13 at 20:36

Matthew

9,851
4
46
77

any idea on how put this value as third column in the table above. like "total"? i'm maybe real noob, but don't have time to learn much now((. – annvio Jan 31 '13 at 21:36
Updated, but originally it sounded like you wanted total of all rows, which would be like @Hamlet's answer – Matthew Jan 31 '13 at 21:41
thank you very much! don't know why i was afraid to put the whole query in select – annvio Jan 31 '13 at 21:45
yeah, i'm experimenting on what statistic i can get from existing data..so that's came up later – annvio Jan 31 '13 at 21:46
`SELECT CHAR_LENGTH('a,b,c,d,') - CHAR_LENGTH(REPLACE('a,b,c,d,', ',', '')) + 1 AS total` This will give me *5*, It should be *4* – Ashwin Parmar Oct 30 '14 at 11:07
1

Nice answer on this – jthalliens May 26 '22 at 20:02

score 10 · Answer 2 · answered Jan 31 '13 at 20:35

10

SELECT SUM(LENGTH(textfield) - LENGTH(REPLACE(textfield, ',', '')) + 1)
  FROM tablename

answered Jan 31 '13 at 20:35

zerkms

249,484
69
436
539

2

Nice, assuming that no field has an empty string. – ypercubeᵀᴹ Jan 31 '13 at 20:38

score 9 · Answer 3 · answered Jan 31 '13 at 21:18

There is a small but significant omission in all answers. All will work only if database character set is utf8 or so, i.e. where symbol , gets one byte. The fact that the LENGTH function returns number of bytes instead of chars. Right answer is to use CHAR_LENGTH which returns number of characters.

SELECT
   SUM(CHAR_LENGTH(textfield) - CHAR_LENGTH(REPLACE(textfield, ',', '')) + 1) cnt
FROM yourTable

score 0 · Answer 4 · answered Jan 31 '13 at 20:36

0

You could use something like this:

select sum(total) TotalWords
from
(
  select length(`text field`) - length(replace(`text field`, ',', '')) + 1 total
  from yourtable
) x

See SQL Fiddle with Demo

answered Jan 31 '13 at 20:36

Taryn

242,637
56
362
405

score 0 · Answer 5 · edited Feb 19 '19 at 09:22

0

All is wrong and doesn't works for me. The only one that work is this bellow

SELECT (length(`textfield`) - length(replace(`textfield`, ',', '')) + 1) as my
FROM yourtable;

This is my fiddle

http://sqlfiddle.com/#!9/d5a8e1/10

edited Feb 19 '19 at 09:22

DiCaprio

823
1
7
24

answered Jan 22 '19 at 18:15

Uziel Almeida Oliveira

3
4

1

don't u think, this is wrong of saying "All is wrong !!!".A lot may have change in `6` years. please review your answer. – Ankish Bansal Jan 22 '19 at 18:40

score 0 · Answer 6 · edited Feb 19 '19 at 09:15

0

SELECT (LENGTH(column_name) - LENGTH(REPLACE(column_name, ',', '')) + 1) as value_count
  FROM table_name

Here LENGTH(column_name) - LENGTH(REPLACE(column_name, ',', '')) gives the number of commas in the value of each column. And +1 with this value provides the number of values separated by comma.

edited Feb 19 '19 at 09:15

Armali

18,255
14
57
171

answered Feb 19 '19 at 08:52

shamir shakir

1
1

score 0 · Answer 7 · answered Jun 04 '20 at 22:44

0

If someone looking for a solution to return 0 for empty fields.

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

answered Jun 04 '20 at 22:44

TRIKONINFOSYSTEMS

530
7
19

Mysql - count values from comma-separated field

7 Answers7