如何计算逗号分隔列表 MySQL 中的项目
所以我的问题很简单:
我在 SQL 中有一个列，它是一个逗号分隔的列表(即 cats,dogs,cows,)我需要使用 only 来计算其中的项目数sql(所以无论我的函数是什么(暂时称之为 fx)都可以这样工作:
I have a column in SQL which is a comma separated list (ie cats,dogs,cows,) I need to count the number of items in it using only sql (so whatever my function is (lets call it fx for now) would work like this:
SELECT fx(fooCommaDelimColumn) AS listCount FROM table WHERE id=...
我知道这是有缺陷的，但你明白了(顺便说一句，如果 fooCommaDelimColumn 的值是 cats,dogs,cows,，那么 listCount 应该返回 4...).
I know that that is flawed, but you get the idea (BTW if the value of fooCommaDelimColumn is cats,dogs,cows,, then listCount should return 4...).
仅此而已.
推荐答案
没有内置函数可以统计字符串中子字符串出现的次数，但是可以计算原始字符串与相同字符串的差值，不用逗号:
There is no built-in function that counts occurences of substring in a string, but you can calculate the difference between the original string, and the same string without commas:
LENGTH(fooCommaDelimColumn) - LENGTH(REPLACE(fooCommaDelimColumn, ',', ''))
它在将近 8 年的时间里被编辑了多次(哇！)，所以为了清楚起见:上面的查询不需要 +1，因为 OPs 数据有一个额外的尾随逗号.
It was edited multiple times over the course of almost 8 years now (wow!), so for sake of clarity: the query above does not need a + 1, because OPs data has an extra trailing comma.
虽然实际上，对于看起来像这样的字符串的一般情况:foo,bar,baz 正确的表达式是
While indeed, in general case for the string that looks like this: foo,bar,baz the correct expression would be
LENGTH(col) - LENGTH(REPLACE(col, ',', '')) + 1
相关文章