Next, split the string, insert a table variable for each character, and group.
Copy codeThe Code is as follows:
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
-- The following functions can be referenced: http://www.cnblogs.com/insus/archive/2011/06/25/2090231.html
IF [dbo]. [IsInteger] (SUBSTRING (@ Value, @ I, 1) = 0
BEGIN
RAISERROR ('the input string contains other characters, not all numbers. ', 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:
Copy codeThe Code is as follows:
EXECUTE [dbo]. [CalNumOfChtInStr] '000000'
Result:
The following text is updated on
The above stored procedure is analyzed. Considering the performance, when determining whether it contains other characters, it loops through a string and inserts each character into the table variable again. The following changes are only made once at this point.
Copy codeThe Code is as follows:
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
-- The following functions can be referenced: http://www.cnblogs.com/insus/archive/2011/06/25/2090231.html
If exists (select top 1 FROM @ dum WHERE [dbo]. [IsInteger] ([Str]) = 0)
BEGIN
RAISERROR ('the input string contains other characters, not all numbers. ', 16,1)
RETURN
END
SELECT [Str], COUNT ([Str]) AS [Num] FROM @ dum group by [Str]
END
The following content is added at on:
If you want to refer to C # version http://www.jb51.net/article/30211.htm