如何计算逗号分隔列表MySQL中的项目 [英] How to count items in comma separated list 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

这篇关于如何计算逗号分隔列表MySQL中的项目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！