Mysql - 从逗号分隔的字段计算值

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

我必须从只读db做一些静态，其中值以奇怪的形式存储

example: I have 2 rows like

例如：我有2行像

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

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

我需要能够算出这是“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.

我没有写入权限，因此不知道如何在不创建功能或类似功能的情况下立即读取和计数。

4 个解决方案

#1

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

SO上的聪明解决方案：如何计算逗号分隔列表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:

是的，您可以选择它作为附加列：并使用@HamletHakobyan的答案中的CHAR_LENGTH进行更正：

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

#2

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

#3

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.

在所有答案中都有一个小但重要的遗漏。只有当数据库字符集大小为utf8时，所有这些都将起作用，即符号获取一个字节。 LENGTH函数返回字节数而不是字符的事实。正确答案是使用CHAR_LENGTH返回字符数。

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

#4

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

请参阅SQL Fiddle with Demo

#1