SQL SERVER 查找某个字符在字符串中出现的次数

　　首先需要建立一个数字辅助表dbo.nums,使用nums表将字符串扩充为字符串长度行，然后对每一行进行字符串截取, 最后统计字符出现的次数.

 

-- Generating Nums Table
-- 来源：SQL SERVER 2005 技术内幕
SET NOCOUNT ON;
USE tempdb;
GO

IF OBJECT_ID('dbo.Nums') IS NOT NULL
  DROP TABLE dbo.Nums;
GO
CREATE TABLE dbo.Nums(n INT NOT NULL PRIMARY KEY);
DECLARE @max AS INT, @rc AS INT;
SET @max = 1000000;
SET @rc = 1;

INSERT INTO Nums VALUES(1);
WHILE @rc * 2 <= @max
BEGIN
  INSERT INTO dbo.Nums SELECT n + @rc FROM dbo.Nums;
  SET @rc = @rc * 2;
END

INSERT INTO dbo.Nums 
  SELECT n + @rc FROM dbo.Nums WHERE n + @rc <= @max;
GO

 

 

 

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        xx
-- Create date: 2011年7月15日21:07:32
-- Description:    查找字符串中某个字符出现的次数
--          思路：使用nums表将字符串扩充为字符串长度行，然后对每一行进行字符串截取，
--                最后统计字符出现的次数                
-- =============================================
CREATE FUNCTION [dbo].[fnQueryCharCountFromString]
(
    @Str    NVARCHAR(MAX),        --待查找字符串
    @Spilt    CHAR(1)                --需查找的字符
)
RETURNS INT
AS
BEGIN
    
    DECLARE @Count    INT
    SELECT @Count = COUNT(*) 
    FROM (
        SELECT SUBSTRING(s,n,1) AS split
        FROM (
            SELECT @Str AS s,n
            FROM dbo.nums
            WHERE n < LEN(@Str)) D
        ) A
    WHERE A.split = @Spilt

    RETURN @Count;

END

 

      如果需要查找某n个字符出现的次数,在SUBSTRING中截取的长度取n即可。