字符串中出现特定字符的次数

Is there MS SQL Server function that counts the number of times a particular character appears in a string?

是否有MS SQL Server函数来统计字符串中出现特定字符的次数?

7 个解决方案

#1

104

There's no direct function for this, but you can do it with a replace:

这里没有直接的函数，但是可以用替换:

declare @myvar varchar(20)
set @myvar = 'Hello World'

select len(@myvar) - len(replace(@myvar,'o',''))

Basically this tells you how many chars were removed, and therefore how many instances of it there were.

基本上，它告诉你有多少字符被移除，因此有多少实例被移除。

Extra:

额外的:

The above can be extended to count the occurences of a multi-char string by dividing by the length of the string being searched for. For example:

可以通过除以要搜索的字符串的长度来扩展上面的内容来计算多字符字符串的出现次数。例如:

declare @myvar varchar(max), @tocount varchar(20)
set @myvar = 'Hello World, Hello World'
set @tocount = 'lo'

select (len(@myvar) - len(replace(@myvar,@tocount,''))) / LEN(@tocount)

#2

Look at the length of the string after replacing the sequence

在替换序列之后查看字符串的长度。

declare @s varchar(10) = 'aabaacaa'
select len(@s) - len(replace(@s, 'a', ''))
>>6

#3

You can do that using replace and len.

你可以用replace和len实现。

Count number of x characters in str:

计算str中x个字符的数量:

len(str) - len(replace(str, 'x', ''))

#4

try that :

试一试:

declare @t nvarchar(max)
set @t='aaaa'

select len(@t)-len(replace(@t,'a',''))

#5

function for sql server:

函数的sql服务器:

CREATE function NTSGetCinC(@Cadena nvarchar(4000), @UnChar nvarchar(100)) 
Returns int 

 as  

 begin 

 declare @t1 int 

 declare @t2 int 

 declare @t3 int 

 set @t1 = len(@Cadena) 

 set @t2 = len(replace(@Cadena,@UnChar,'')) 

 set @t3 = len(@UnChar) 


 return (@t1 - @t2)  / @t3 

 end

Code for visual basic and others:

visual basic和其他代码:

Public Function NTSCuentaChars(Texto As String, CharAContar As String) As Long

NTSCuentaChars = (Len(Texto) - Len(Replace(Texto, CharAContar, ""))) / Len(CharAContar)

End Function

#6

Use this function begining from SQL SERVER 2016

使用这个从SQL SERVER 2016开始的函数

Select Count(value) From STRING_SPLIT('AAA AAA AAA',' ');

-- Output : 3

When This function used with count function it gives you how many character exists in string

当这个函数与count函数一起使用时，它会给出字符串中有多少字符

#7

You may do this completely in-line by replacing the desired character with an empty string, calling LENGTH function and substracting from the original string's length.

您可以使用空字符串替换所需的字符，调用LENGTH函数，并从原始字符串的长度进行减法。

SELECT 
  CustomerName, 
  LENGTH(CustomerName) -
  LENGTH(REPLACE(CustomerName, ' ', '')) AS NumberOfSpaces
FROM Customers;

#1

104