How can you find the number of occurrences of a particular character in a string using sql?
如何使用sql找到字符串中特定字符的出现次数？
Example: I want to find the number of times the letter ‘d’ appears in this string.
示例：我想查找字母“d”出现在此字符串中的次数。
declare @string varchar(100)
select @string = 'sfdasadhfasjfdlsajflsadsadsdadsa'
2 个解决方案
#1
53
Here you go:
干得好：
declare @string varchar(100)
select @string = 'sfdasadhfasjfdlsajflsadsadsdadsa'
SELECT LEN(@string) - LEN(REPLACE(@string, 'd', '')) AS D_Count
#2
12
If you want to make it a little more general, you should divide by the length of the thing you're looking for. Like this:
如果你想让它更通用一点，你应该除以你想要的东西的长度。喜欢这个：
declare @searchstring varchar(10);
set @searchstring = 'Rob';
select original_string,
(len(orginal_string) - len(replace(original_string, @searchstring, ''))
/ len(@searchstring)
from someTable;
This is because each time you find 'Rob', you remove three characters. So when you remove six characters, you've found 'Rob' twice.
这是因为每次找到“Rob”时，都会删除三个字符。所以当你删除六个字符时，你已经发现了两次'Rob'。
#1
53
Here you go:
干得好：
declare @string varchar(100)
select @string = 'sfdasadhfasjfdlsajflsadsadsdadsa'
SELECT LEN(@string) - LEN(REPLACE(@string, 'd', '')) AS D_Count
#2
12
If you want to make it a little more general, you should divide by the length of the thing you're looking for. Like this:
如果你想让它更通用一点，你应该除以你想要的东西的长度。喜欢这个：
declare @searchstring varchar(10);
set @searchstring = 'Rob';
select original_string,
(len(orginal_string) - len(replace(original_string, @searchstring, ''))
/ len(@searchstring)
from someTable;
This is because each time you find 'Rob', you remove three characters. So when you remove six characters, you've found 'Rob' twice.
这是因为每次找到“Rob”时，都会删除三个字符。所以当你删除六个字符时，你已经发现了两次'Rob'。