SQL中获取一个关键字在某个字符串出现次数

判断一个关键字在另一个字符串中出现的次数

GO
-- 思路1：将字符串替换掉为空字符，然后计算替换的长度，除以关键字的长度，结果就是出现长度
CREATE FUNCTION RunCount
(
@Souce NVARCHAR(4000),
@Keyword NVARCHAR(4000) 
)
RETURNS INT
AS
BEGIN
DECLARE @NewString NVARCHAR(4000)
DECLARE @SouceLength INT
DECLARE @NewLength INT
DECLARE @KeywordLength INT
SET @NewString=REPLACE(@Souce,@Keyword,'')
SET @SouceLength=LEN(@Souce)
SET @NewLength=LEN(@NewString)
SET @KeywordLength=LEN(@Keyword)
IF(@KeywordLength<=0) SET @KeywordLength=1
RETURN (@SouceLength-@NewLength)/(@KeywordLength)  
END

GO

SELECT dbo.RunCount('aaaaaa','a')

GO
DROP FUNCTION RunCount

go
-- 思路2：将字符串循环，判断出现次数
CREATE FUNCTION RunCount
(
@Souce NVARCHAR(4000),
@Keyword NVARCHAR(4000) 
)
RETURNS INT
AS
BEGIN
DECLARE @COUNT INT  
SET @COUNT=0
    WHILE(CHARINDEX(@Keyword,@Souce)>0)
    BEGIN
    SET @COUNT=@COUNT+1
    SET @Souce=SUBSTRING(@Souce,CHARINDEX(@Keyword,@Souce)+1,LEN(@Souce)-CHARINDEX(@Keyword,@Souce))
    END
RETURN @COUNT
END

GO

SELECT dbo.RunCount('aaaaaa','a')

GO
DROP FUNCTION RunCount