如何计算逗号分隔列表 MySQL 中的项目

2021-11-20 00:00:00 sql 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

相关文章