如何计算SQL varchar中某个子字符串的出现次数?

第一种方法是间接地将逗号替换为空字符串并比较长度。

Declare @string varchar(1000)
Set @string = 'a,b,c,d'
select len(@string) - len(replace(@string, ',', ''))

CMSJR答案的快速扩展,适用于多个字符的字符串。

CREATE FUNCTION dbo.CountOccurrencesOfString
(
    @searchString nvarchar(max),
    @searchTerm nvarchar(max)
)
RETURNS INT
AS
BEGIN
    return (LEN(@searchString)-LEN(REPLACE(@searchString,@searchTerm,'')))/LEN(@searchTerm)
END

用途:

SELECT * FROM MyTable
where dbo.CountOccurrencesOfString(MyColumn, 'MyString') = 1

可以将字符串长度与删除逗号的字符串长度进行比较:

len(value) - len(replace(value,',',''))

@csmjr的答案在某些情况下有问题。

他的回答是:

Declare @string varchar(1000)
Set @string = 'a,b,c,d'
select len(@string) - len(replace(@string, ',', ''))

但是,这在大多数情况下都有效,请尝试运行:

DECLARE @string VARCHAR(1000)
SET @string = 'a,b,c,d ,'
SELECT LEN(@string) - LEN(REPLACE(@string, ',', ''))

出于某种原因,replace去掉了最后一个逗号,也去掉了前面的空格(不知道为什么)。当期望值为4时,返回值为5。还有一种方法可以做到这一点,即使在这种特殊情况下也可以做到:

DECLARE @string VARCHAR(1000)
SET @string = 'a,b,c,d ,'
SELECT LEN(REPLACE(@string, ',', '**')) - LEN(@string)

请注意,您不需要使用星号。任何两个字符的替换都可以。这样做的目的是,对于要计数的字符的每个实例,将字符串加长一个字符,然后减去原始字符的长度。这基本上是与原始答案相反的方法,而不是带有奇怪的修剪副作用。

基于@andrew的解决方案,使用非过程表值函数和交叉应用可以获得更好的性能:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*  Usage:
    SELECT t.[YourColumn], c.StringCount
    FROM YourDatabase.dbo.YourTable t
        CROSS APPLY dbo.CountOccurrencesOfString('your search string',     t.[YourColumn]) c
*/
CREATE FUNCTION [dbo].[CountOccurrencesOfString]
(
    @searchTerm nvarchar(max),
    @searchString nvarchar(max)

)
RETURNS TABLE
AS
    RETURN 
    SELECT (DATALENGTH(@searchString)-DATALENGTH(REPLACE(@searchString,@searchTerm,'')))/NULLIF(DATALENGTH(@searchTerm), 0) AS StringCount

Declare @string varchar(1000)

DECLARE @SearchString varchar(100)

Set @string = 'as as df df as as as'

SET @SearchString = 'as'

select ((len(@string) - len(replace(@string, @SearchString, ''))) -(len(@string) - 
        len(replace(@string, @SearchString, ''))) % 2)  / len(@SearchString)

接受的答案是正确的, 将其扩展为在子字符串中使用2个或更多字符:

Declare @string varchar(1000)
Set @string = 'aa,bb,cc,dd'
Set @substring = 'aa'
select (len(@string) - len(replace(@string, @substring, '')))/len(@substring)

DECLARE @records varchar(400)
SELECT @records = 'a,b,c,d'
select  LEN(@records) as 'Before removing Commas' , LEN(@records) - LEN(REPLACE(@records, ',', '')) 'After Removing Commans'

我认为达雷尔李有一个很好的答案。替换 CHARINDEX() 具有 PATINDEX() 你可以做些弱者 regex 也在搜索字符串…

比如说你用这个 @pattern :

set @pattern='%[-.|!,'+char(9)+']%'

你为什么要做这种疯狂的事?

假设您正在将分隔文本字符串加载到临时表中,其中包含数据的字段类似于varchar(8000)或nvarchar(max)…

有时,用数据而不是ETL(提取转换加载)进行ELT(提取加载转换)比用数据进行ELT(提取转换加载)更容易/更快,一种方法是将分隔的记录按原样加载到临时表中,特别是如果您可能希望使用更简单的方法来查看异常记录,而不是将它们作为SSIS包的一部分来处理……但这是一个神圣的任务。R代表不同的线。

下面应该为单字符和多字符搜索提供技巧:

CREATE FUNCTION dbo.CountOccurrences
(
   @SearchString VARCHAR(1000),
   @SearchFor    VARCHAR(1000)
)
RETURNS TABLE
AS
   RETURN (
             SELECT COUNT(*) AS Occurrences
             FROM   (
                       SELECT ROW_NUMBER() OVER (ORDER BY O.object_id) AS n
                       FROM   sys.objects AS O
                    ) AS N
                    JOIN (
                            VALUES (@SearchString)
                         ) AS S (SearchString)
                         ON
                         SUBSTRING(S.SearchString, N.n, LEN(@SearchFor)) = @SearchFor
          );
GO

---------------------------------------------------------------------------------------
-- Test the function for single and multiple character searches
---------------------------------------------------------------------------------------
DECLARE @SearchForComma      VARCHAR(10) = ',',
        @SearchForCharacters VARCHAR(10) = 'de';

DECLARE @TestTable TABLE
(
   TestData VARCHAR(30) NOT NULL
);

INSERT INTO @TestTable
     (
        TestData
     )
VALUES
     ('a,b,c,de,de ,d e'),
     ('abc,de,hijk,,'),
     (',,a,b,cde,,');

SELECT TT.TestData,
       CO.Occurrences AS CommaOccurrences,
       CO2.Occurrences AS CharacterOccurrences
FROM   @TestTable AS TT
       OUTER APPLY dbo.CountOccurrences(TT.TestData, @SearchForComma) AS CO
       OUTER APPLY dbo.CountOccurrences(TT.TestData, @SearchForCharacters) AS CO2;

可以使用数字表(dbo.nums)将函数简化一点:

   RETURN (
             SELECT COUNT(*) AS Occurrences
             FROM   dbo.Nums AS N
                    JOIN (
                            VALUES (@SearchString)
                         ) AS S (SearchString)
                         ON
                         SUBSTRING(S.SearchString, N.n, LEN(@SearchFor)) = @SearchFor
          );

如果我们知道长度和空间有限制,为什么不能先替换空间呢? 那么我们就知道没有空间来迷惑len了。

len(replace(@string, ' ', '-')) - len(replace(replace(@string, ' ', '-'), ',', ''))

使用这个代码,它工作得很好。 我已经创建了一个接受两个参数的SQL函数,第一个参数是我们要搜索的长字符串,它可以接受长度达1500个字符的字符串(当然,您可以扩展它,甚至将其更改为文本数据类型)。 第二个参数是我们要计算它发生的次数的子字符串(它的长度可达200个字符,当然您可以将其更改为您需要的值)。输出是一个整数,代表频率的数目……尽情享受吧。

CREATE FUNCTION [dbo].[GetSubstringCount]
(
  @InputString nvarchar(1500),
  @SubString NVARCHAR(200)
)
RETURNS int
AS
BEGIN 
        declare @K int , @StrLen int , @Count int , @SubStrLen int 
        set @SubStrLen = (select len(@SubString))
        set @Count = 0
        Set @k = 1
        set @StrLen =(select len(@InputString))
    While @K <= @StrLen
        Begin
            if ((select substring(@InputString, @K, @SubStrLen)) = @SubString)
                begin
                    if ((select CHARINDEX(@SubString ,@InputString)) > 0)
                        begin
                        set @Count = @Count +1
                        end
                end
                                Set @K=@k+1
        end
        return @Count
end

我最后编写了这个函数,它应该涵盖所有可能的情况,在输入中添加一个字符前缀和后缀。此字符的计算结果与搜索参数中包含的任何字符都不同,因此它不会影响结果。

CREATE FUNCTION [dbo].[CountOccurrency]
(
@Input nvarchar(max),
@Search nvarchar(max)
)
RETURNS int AS
BEGIN
    declare @SearhLength as int = len('-' + @Search + '-') -2;
    declare @conteinerIndex as int = 255;
    declare @conteiner as char(1) = char(@conteinerIndex);
    WHILE ((CHARINDEX(@conteiner, @Search)>0) and (@conteinerIndex>0))
    BEGIN
        set @conteinerIndex = @conteinerIndex-1;
        set @conteiner = char(@conteinerIndex);
    END;
    set @Input = @conteiner + @Input + @conteiner
    RETURN (len(@Input) - len(replace(@Input, @Search, ''))) / @SearhLength
END 

使用

select dbo.CountOccurrency('a,b,c,d ,', ',')

可以使用以下存储过程获取、值。

IF  EXISTS (SELECT * FROM sys.objects 
WHERE object_id = OBJECT_ID(N'[dbo].[sp_parsedata]') AND type in (N'P', N'PC'))
    DROP PROCEDURE [dbo].[sp_parsedata]
GO
create procedure sp_parsedata
(@cid integer,@st varchar(1000))
as
  declare @coid integer
  declare @c integer
  declare @c1 integer
  select @c1=len(@st) - len(replace(@st, ',', ''))
  set @c=0
  delete from table1 where complainid=@cid;
  while (@c<=@c1)
    begin
      if (@c<@c1) 
        begin
          select @coid=cast(replace(left(@st,CHARINDEX(',',@st,1)),',','') as integer)
          select @st=SUBSTRING(@st,CHARINDEX(',',@st,1)+1,LEN(@st))
        end
      else
        begin
          select @coid=cast(@st as integer)
        end
      insert into table1(complainid,courtid) values(@cid,@coid)
      set @c=@c+1
    end

replace/len测试很可爱,但可能效率很低(特别是在内存方面)。 一个带有循环的简单函数就可以完成这项工作。

CREATE FUNCTION [dbo].[fn_Occurences] 
(
    @pattern varchar(255),
    @expression varchar(max)
)
RETURNS int
AS
BEGIN

    DECLARE @Result int = 0;

    DECLARE @index BigInt = 0
    DECLARE @patLen int = len(@pattern)

    SET @index = CHARINDEX(@pattern, @expression, @index)
    While @index > 0
    BEGIN
        SET @Result = @Result + 1;
        SET @index = CHARINDEX(@pattern, @expression, @index + @patLen)
    END

    RETURN @Result

END

也许您不应该这样存储数据。在字段中存储逗号分隔的列表是一种糟糕的做法。查询效率非常低。这应该是一个相关的表。