接下來(lái)拆分這一串字符串，每個(gè)字符插入一個(gè)表變量中，最后使用GROUP BY進(jìn)行分組。
復(fù)制代碼 代碼如下:
CalNumOfChtInStr
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Insus.NET
-- Create date: 2012-02-23
-- Description: Calculate the number of characters in the string
-- =============================================
CREATE PROCEDURE [dbo].[CalNumOfChtInStr]
(
@Value NVARCHAR(MAX)
)
AS
BEGIN
DECLARE @dum TABLE ([Str] NVARCHAR(2))
DECLARE @I INT = LEN(@Value),@J INT = LEN(@Value)
WHILE @I > 0
BEGIN
--以下函數(shù)可參考：http://www.cnblogs.com/insus/archive/2011/06/25/2090231.html
IF [dbo].[IsInteger] (SUBSTRING(@Value, @I, 1)) = 0
BEGIN
RAISERROR('傳入字符串包含其它字符，不完全是數(shù)字。',16,1)
RETURN
END
SET @I = @I - 1
END
WHILE @J > 0
BEGIN
INSERT INTO @dum VALUES(SUBSTRING(@Value, @J, 1))
SET @J = @J - 1
END
SELECT [Str],COUNT([Str]) AS [Num] FROM @dum GROUP BY [Str]
END
Demo:
復(fù)制代碼 代碼如下:
EXECUTE [dbo].[CalNumOfChtInStr] '5487554127489423454'
結(jié)果：
以下文字更新于2012-02-24 09:40
分析以上的存儲(chǔ)過(guò)程，考慮到性能問(wèn)題，它在判斷是否包含有其它字符時(shí)，循環(huán)一次字符串，然后又循環(huán)一次將每一個(gè)字符插入表變量中。以下修改正此點(diǎn)只做循環(huán)一次。
復(fù)制代碼 代碼如下:
CalNumOfChtInStr
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Insus.NET
-- Create date: 2012-02-23
-- Update date: 2012-02-24
-- Description: Calculate the number of characters in the string
-- =============================================
ALTER PROCEDURE [dbo].[CalNumOfChtInStr]
(
@Value NVARCHAR(MAX)
)
AS
BEGIN
DECLARE @dum TABLE ([Str] NVARCHAR(2))
DECLARE @I INT = LEN(@Value)
WHILE @I > 0
BEGIN
INSERT INTO @dum VALUES(SUBSTRING(@Value, @I, 1))
SET @I = @I - 1
END
--以下函數(shù)可參考：http://www.cnblogs.com/insus/archive/2011/06/25/2090231.html
IF EXISTS(SELECT TOP 1 1 FROM @dum WHERE [dbo].[IsInteger]([Str]) = 0)
BEGIN
RAISERROR('傳入字符串包含其它字符，不完全是數(shù)字。',16,1)
RETURN
END
SELECT [Str],COUNT([Str]) AS [Num] FROM @dum GROUP BY [Str]
END
以下內(nèi)容于2012-04-29 10:44分添加：
如果想?yún)⒖糃#版本 https://www.jb51.net/article/30211.htm
