用TSQL求子串在父串中出现的次数

　导言

　　由于SQL Server本身没提供计算一个字符串在另一个字符串重复次数的函数，大家按照自己的思路使用自定义函数实现了该功能，并在网上传播。我阅读了同事从网上获取的该函数的一个版本后，便发现该函数存在一个明显的逻辑错误。为了进一步确认这个问题，我在Google上搜索相关关键字，发现该功能多数的实现思路一致，但大多数都存在这个共同的逻辑错误。可见从网络上获取的一些资源，可以作为参考，但要在实际需求中能够应用，是需要仔细检查的。

　　功能实现

　　常见方法

　　以下是网上一种较常见的函数实现：

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

--描述：利用循环得到一个字符串在另一个字符串中出现的次数
--DEMO:
-- SELECT dbo.f_getcharcount_false('df ththth','tht')
CREATE FUNCTION [dbo].[f_getcharcount_false](
@str varchar(8000),
@chr varchar(8000)
) RETURNS INT
AS
BEGIN
DECLARE @re INT,@i INT
SELECT @re=0,@i=charindex(@chr,@str)+1
WHILE @i>1
     SELECT @re=@re+1
         ,@str=substring(@str,@i,8000)
         ,@i=charindex(@chr,@str)+1

RETURN(@re)
END

 

　　上面的函数设计思路是循环截断源字符串，通过计算截断的次数来获取子字符串出现的重复数。思路当然是可行的，但是在它截断的时候，起点是@i=charindex(@chr,@str)+1，这里就是错误的起因。以我给出的DEMO来讲，代码如下：

　　SELECT dbo.f_getcharcount_false('df ththth','tht')

　　结果为2，其实明显，正确的结果是1。因为上述函数的截断内容只有“t”，而后面的“ht”会被继续使用，导致“t”与后面的“ht”组合，结果计数多了一次。正确的截取位置应该从子串的位置结束处开始，就是从“tht”后面开始。

　　既然思路正确，那么我们就修改上述代码中的错误，修改后的代码如下：第1页：null 第2页：null 第3页：null 。

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

--描述：利用循环得到一个字符串在另一个字符串中出现的次数
--DEMO:
--  SELECT dbo.f_getcharcount_true('df ththth','tht')
CREATE FUNCTION [dbo].[f_getcharcount_true](
@str varchar(8000),
@chr varchar(8000)
) RETURNS INT
AS
BEGIN
DECLARE @re INT,@i INT
SELECT @re=0,@i=charindex(@chr,@str)+1
WHILE @i>1
     SELECT @re=@re+1
         ,@str=substring(@str,@i-1+len(@chr),8000)
         ,@i=charindex(@chr,@str)+1
RETURN(@re)
END

 

　　当然，截断字符串很多人喜欢用stuff填充空字符，但网上流传的该版本，用stuff函数实现截断操作的，多数也是存在这个截断位置错误的。

　　优化的方法

　　上面讲的只是在网络上流传最广泛的方法，那么想要精炼代码，寻找更便捷和高效的方法也是可行。下面再介绍两种较便捷的实现方式。

　　<方法一>

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/*
描述:
 利用字符串长度计算字符出现次数
DEMO:
 SELECT dbo.f_get_char_repeat_count('df ththth','tht')
*/
CREATE FUNCTION [dbo].[f_get_char_repeat_count]
(@str VARCHAR(8000),
@substr VARCHAR(8000)
)
RETURNS INT
BEGIN
 RETURN (LEN(@str)-LEN(REPLACE(@str,@substr,'')))/LEN(@substr)
END

 

第1页：null 第2页：null 第3页：null 。

　　<方法二>

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/*
描述：利用长度差值求重复次数
DEMO:
 SELECT fn_get_char_repeat_count_by_add_char('ahahaahde','ah')
*/
CREATE FUNCTION [dbo].[fn_get_char_repeat_count_by_add_char](
@str varchar(8000),--父串
@chr varchar(8000) --子串
) RETURNS INT
AS
BEGIN
RETURN(len(replace(@str,@chr,@chr+' '))-len(@str))
END

 

　　上面的<方法一>是用空字符替换父字符串中出现的子串，用剩余字符串的长度与子字符串的长度求商;<方法二>是把为父字符串中出现子字符串的地方都增加个空格，用替换后的父字符串长度减去原父字符串的长度。

 

本文转自：http://www.techrss.cn/html/2008/03-30/78902.htm