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

  http://database.ctocio.com.cn/tips/121/8055121.shtml

用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”后面开始。

既然思路正确，那么我们就修改上述代码中的错误，修改后的代码如下：

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

< 方法二 >

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

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

总结
    面对日常的工作需求，针对具体的问题，关键在于思路的不同。网络上丰富的资源是可以借鉴的，但同时要注意辨别这些资源的可用性。网络资源的作用主要给我们借鉴，扩展我们的思路，不应该直接投入使用。