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 
--以下函數可參考：http://www.cnblogs.com/insus/archive/2011/06/25/2090231.html 
IF [dbo].[IsInteger] (SUBSTRING(@Value, @I, 1)) = 0 
BEGIN 
RAISERROR('傳入字符串包含其它字符，不完全是數字。',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: 
 代碼如下:

EXECUTE [dbo].[CalNumOfChtInStr] '5487554127489423454' 


結果：

以下文字更新于2012-02-24 09:40 
分析以上的存儲過程，考慮到性能問題，它在判斷是否包含有其它字符時，循環一次字符串，然后又循環一次將每一個字符插入表變量中。以下修改正此點只做循環一次。 
 代碼如下:

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 
--以下函數可參考：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('傳入字符串包含其它字符，不完全是數字。',16,1) 
RETURN 
END 
SELECT [Str],COUNT([Str]) AS [Num] FROM @dum GROUP BY [Str] 
END 
 
以下內容于2012-04-29 10:44分添加： 
如果想參考C#版本 //www.gxlcms.com/article/30211.htm
 
 




聲明：本網頁內容旨在傳播知識，若有侵權等問題請及時與本網聯系，我們將在第一時間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com