计算一串数字中每个数字出现的次数,可以这样子,先判断输入的字符串是不是数字组成,还是否包含有其它字符。
接下来拆分这一串字符串,每个字符插入一个表变量中,最后使用GROUP BY进行分组。
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
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
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
BEGIN
RAISERROR( ' 传入字符串包含其它字符,不完全是数字。 ', 16, 1)
RETURN
END
SELECT [ Str ], COUNT( [ Str ]) AS [ Num ] FROM @dum GROUP BY [ Str ]
END
以下内容于2012-04-29 10:44分添加:
如果想参考C#版本:http://www.cnblogs.com/insus/archive/2012/04/29/2475988.html