如何计算字符在SQL列中出现的次数？

For a user logging table I have in a SQL database, I track the some of the parameters off of a report request. The report allows multiple ID's to be passed to it and I store all of those in a single column in the database column. If this were to be a normalized set of data, there would definitely be an additional table setup for this, but this is what was inherited...

对于我在SQL数据库中的用户日志记录表，我跟踪报告请求中的一些参数。该报告允许将多个ID传递给它，并将所有这些ID存储在数据库列的单个列中。如果这是一个标准化的数据集，肯定会有一个额外的表设置，但这是继承的...

I've now been asked to give a quick count of the number of times a report was run with more than 2 ID's passed to it. I can easily get the number of records that have more than 1 report requested because they all include a comma.

我现在被要求快速计算报告运行的次数，并且传递了超过2个ID。我可以很容易地获得请求超过1个报告的记录数，因为它们都包含逗号。

What I need to do next is count the number of times a comma appears in a column. How do you do this in SQL?

我接下来需要做的是计算逗号在列中出现的次数。你是如何在SQL中做到这一点的？

--count the number of times more than 1 report was requested in the record
select 
    count(*) as cnt
from
    [table]
where
    RequestedReportParams Like '%,%'

2 个解决方案

#1

SELECT LEN(RequestedReportParams) - LEN(REPLACE(RequestedReportParams, ',', ''))
FROM YourTable
WHERE .....

This is simply comparing the length of the column with the commas, with the length of the value with the commas removed, to give you the difference (i.e. the number of commas)

这只是比较列的长度和逗号，删除逗号的值的长度，以给出差异（即逗号的数量）

#2

It seems the quick and dirty way to answer the question you've been asked would be to do this:

似乎回答你被问到的问题的快速而肮脏的方法是这样做：

select 
    count(*) as cnt
FROM 
    [table]
WHERE 
    RequestedReportParams Like '%,%,%'

#1

SELECT LEN(RequestedReportParams) - LEN(REPLACE(RequestedReportParams, ',', ''))
FROM YourTable
WHERE .....