如何计算 SQL 列中字符的实例数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1860457/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to count instances of character in SQL Column
提问by cindi
I have an sql column that is a string of 100 'Y' or 'N' characters. For example:
我有一个 sql 列，它是一个由 100 个 'Y' 或 'N' 字符组成的字符串。例如：
YYNYNYYNNNYYNY...
YYNYNYYNNNYYNY...
What is the easiest way to get the count of all 'Y' symbols in each row.
获取每行中所有“Y”符号的计数的最简单方法是什么。
采纳答案by dxh
In SQL Server:
在 SQL Server 中：
SELECT LEN(REPLACE(myColumn, 'N', ''))
FROM ...
回答by nickf
This snippet works in the specific situation where you have a boolean: it answers "how many non-Ns are there?".
此代码段适用于您有布尔值的特定情况：它回答“有多少非 N？”。
SELECT LEN(REPLACE(col, 'N', ''))
If, in a different situation, you were actually trying to count the occurrences of a certain character (for example 'Y') in any given string, use this:
如果在不同的情况下，您实际上是在尝试计算任何给定字符串中某个字符（例如“Y”）的出现次数，请使用以下命令：
SELECT LEN(col) - LEN(REPLACE(col, 'Y', ''))
回答by Ron Sell
This gave me accurate results every time...
这每次都给了我准确的结果......
This is in my Stripes field...
这是在我的条纹领域...
Yellow, Yellow, Yellow, Yellow, Yellow, Yellow, Black, Yellow, Yellow, Red, Yellow, Yellow, Yellow, Black
黄色，黄色，黄色，黄色，黄色，黄色，黑色，黄色，黄色，红色，黄色，黄色，黄色，黑色
- 11 Yellows
- 2 Black
- 1 Red
- 11 黄色
- 2 黑色
- 1 红色
SELECT (LEN(Stripes) - LEN(REPLACE(Stripes, 'Red', ''))) / LEN('Red')
FROM t_Contacts
回答by Aaron Dake
DECLARE @StringToFind VARCHAR(100) = "Text To Count"
SELECT (LEN([Field To Search]) - LEN(REPLACE([Field To Search],@StringToFind,'')))/COALESCE(NULLIF(LEN(@StringToFind), 0), 1) --protect division from zero
FROM [Table To Search]
回答by Jason Punyon
Maybe something like this...
也许像这样......
SELECT
LEN(REPLACE(ColumnName, 'N', '')) as NumberOfYs
FROM
SomeTable
回答by Mayuresh Bhabal
The easiest way is by using Oracle function:
最简单的方法是使用 Oracle 函数：
SELECT REGEXP_COUNT(COLUMN_NAME,'CONDITION') FROM TABLE_NAME
回答by Himanshu Tiwari
Try This. It determines the no. of single character occurrences as well as the sub-string occurrences in main string.
尝试这个。它决定了没有。单个字符出现次数以及主字符串中的子字符串出现次数。
SELECT COUNT(DECODE(SUBSTR(UPPER(:main_string),rownum,LENGTH(:search_char)),UPPER(:search_char),1)) search_char_count
FROM DUAL
connect by rownum <= length(:main_string);
回答by user3469285
If you want to count the number of instances of strings with more than a single character, you can either use the previous solution with regex, or this solution uses STRING_SPLIT, which I believe was introduced in SQL Server 2016. Also you'll need compatibility level 130 and higher.
如果您想计算具有多个字符的字符串实例的数量，您可以使用带有正则表达式的先前解决方案，或者此解决方案使用 STRING_SPLIT，我相信它是在 SQL Server 2016 中引入的。此外，您还需要兼容性130级及以上。
ALTER DATABASE [database_name] SET COMPATIBILITY_LEVEL = 130
.
.
--some data
DECLARE @table TABLE (col varchar(500))
INSERT INTO @table SELECT 'whaCHAR(10)teverCHAR(10)whateverCHAR(10)'
INSERT INTO @table SELECT 'whaCHAR(10)teverwhateverCHAR(10)'
INSERT INTO @table SELECT 'whaCHAR(10)teverCHAR(10)whateverCHAR(10)~'
--string to find
DECLARE @string varchar(100) = 'CHAR(10)'
--select
SELECT
col
, (SELECT COUNT(*) - 1 FROM STRING_SPLIT (REPLACE(REPLACE(col, '~', ''), 'CHAR(10)', '~'), '~')) AS 'NumberOfBreaks'
FROM @table
回答by Statsanalyst
The second answer provided by nickf is very clever. However, it only works for a character length of the target sub-string of 1 and ignores spaces. Specifically, there were two leading spaces in my data, which SQL helpfully removes (I didn't know this) when all the characters on the right-hand-side are removed. Which meant that
nickf 提供的第二个答案非常聪明。但是，它仅适用于目标子字符串的字符长度为 1 并忽略空格。具体来说，我的数据中有两个前导空格，当右侧的所有字符都被删除时，SQL 会帮助删除（我不知道这一点）。这意味着
" John Smith"
“ 约翰·史密斯”
generated 12 using Nickf's method, whereas:
使用 Nickf 的方法生成 12，而：
" Joe Bloggs, John Smith"
“乔·布洛格斯，约翰·史密斯”
generated 10, and
生成 10，和
" Joe Bloggs, John Smith, John Smith"
“乔·布洛格斯，约翰·史密斯，约翰·史密斯”
Generated 20.
生成 20。
I've therefore modified the solution slightly to the following, which works for me:
因此，我将解决方案稍微修改为以下内容，这对我有用：
Select (len(replace(Sales_Reps,' ',''))- len(replace((replace(Sales_Reps, ' ','')),'JohnSmith','')))/9 as Count_JS
I'm sure someone can think of a better way of doing it!
我相信有人可以想到更好的方法！
回答by atik sarker
You can also Try This
你也可以试试这个
-- DECLARE field because your table type may be text
DECLARE @mmRxClaim nvarchar(MAX)
-- Getting Value from table
SELECT top (1) @mmRxClaim = mRxClaim FROM RxClaim WHERE rxclaimid_PK =362
-- Main String Value
SELECT @mmRxClaim AS MainStringValue
-- Count Multiple Character for this number of space will be number of character
SELECT LEN(@mmRxClaim) - LEN(REPLACE(@mmRxClaim, 'GS', ' ')) AS CountMultipleCharacter
-- Count Single Character for this number of space will be one
SELECT LEN(@mmRxClaim) - LEN(REPLACE(@mmRxClaim, 'G', '')) AS CountSingleCharacter
Output:
输出：
