博客好久没更新了。公司事情多，所以很多时间给用在公司的事情上了。从进公司到现在，确确实实遇到很多的问题。后期一定会和大家多多分享。先从最近的一件事情说起，公司信息管理部门，需要我统计一份用户真实姓名（User.TrueName）的数据，按如下方式统计。
1、 中文 数量2、 英文 数量3、 中文+英文 数量4、 数字 数量5、 数字+英文或+中文 数量
SQL Server 有通配符和正则两种方式，通配符匹配的关系一般都很简单。而正则可以解决大部分的问题。对于上面一种统计，我是按最基本的SQL写法来的。
View Code USE SCM DECLARE @index INT , @End INT, @TrueName NVARCHAR( 20), @UserID INT, @t_UserID INT, @CharLength INT DECLARE @Digit INT, @Letter INT , @Hanzi INT, @LetterAndHanzi INT, @DigitAndLetterOrHanzi INT SET @index = 0 SET @UserID = 0 SET @t_UserID = 0 SET @CharLength = 0 SET @Digit = 0 SET @Letter = 0 SET @Hanzi = 0 SET @LetterAndHanzi = 0 SET @DigitAndLetterOrHanzi = 0 SELECT @End = COUNT( 1) FROM Users(NOLOCK) u WHERE u.TrueName <> '' WHILE( @index < @End) BEGIN DECLARE @LetterChar CHAR( 2), @LetterIndex INT, @TempChar NVARCHAR( 20) DECLARE @t_Digit INT, @t_Letter INT , @t_Hanzi INT SET @LetterIndex = 1 SET @LetterChar = '' SET @TempChar = '' SET @t_Digit = 0 SET @t_Letter = 0 SET @t_Hanzi = 0 SELECT TOP 1 @TrueName =u.TrueName, @t_UserID =u.UserID FROM Users(nolock) u WHERE u.TrueName <> '' AND u.UserID > @UserID ORDER BY u.UserID IF( @TrueName <> '') BEGIN -- PRINT @TrueName+'--------'+CAST(@t_UserID AS varchar) -- PRINT '@@'+CAST(@index AS VARCHAR) SET @CharLength = LEN( @TrueName) -- WHERE IF( @CharLength > 0) BEGIN WHILE( @LetterIndex <= @CharLength) BEGIN SET @LetterChar = SUBSTRING( @TrueName, @LetterIndex, 1) -- SET @TempChar = @TempChar+@LetterChar+'-' -- 分析单个字符 DECLARE @Unicode INT SET @Unicode = UNICODE( @LetterChar) SET @LetterIndex = @LetterIndex + 1 IF @Unicode BETWEEN 48 AND 57 BEGIN -- 数字 SET @t_Digit = @t_Digit + 1 CONTINUE END IF @Unicode BETWEEN 65 AND 90 BEGIN -- 字母 SET @t_Letter = @t_Letter + 1 CONTINUE END IF @Unicode BETWEEN 97 AND 122 BEGIN -- 字母 SET @t_Letter = @t_Letter + 1 CONTINUE END IF @Unicode > 127 BEGIN -- 汉字 SET @t_Hanzi = @t_Hanzi + 1 CONTINUE END END -- PRINT CAST(@t_UserID AS varchar)+'-->'+@TrueName+'-->'+@TempChar END IF @t_Digit = @CharLength BEGIN SET @Digit = @Digit + 1 END IF @t_Letter = @CharLength BEGIN -- PRINT @TrueName +'-->>' + CAST(@CharLength AS VARCHAR) SET @Letter = @Letter + 1 END IF @t_Hanzi = @CharLength BEGIN SET @Hanzi = @Hanzi + 1 END IF ( @t_Letter <> 0 AND @t_Hanzi <> 0) AND @t_Letter + @t_Hanzi = @CharLength BEGIN SET @LetterAndHanzi = @LetterAndHanzi + 1 END IF ( ( @t_Digit <> 0 AND @t_Letter <> 0) AND ( @t_Digit + @t_Letter = @CharLength )) OR ( ( @t_Digit <> 0 AND @t_Hanzi <> 0) AND ( @t_Digit + @t_Hanzi = @CharLength) ) OR ( ( @t_Digit <> 0 AND @t_Letter <> 0 AND @t_Hanzi <> 0 ) AND ( @t_Digit + @t_Letter + @t_Hanzi = @CharLength)) BEGIN SET @DigitAndLetterOrHanzi = @DigitAndLetterOrHanzi + 1 END SET @CharLength = 0 SET @TempChar = '' SET @t_Digit = 0 SET @t_Letter = 0 SET @t_Hanzi = 0 END SET @index = @index + 1 SET @UserID = @t_UserID END PRINT ' 中文: ' + cast( @Hanzi as VARCHAR) PRINT ' 英文: ' + cast( @Letter as VARCHAR) PRINT ' 中文+英文: ' + cast( @LetterAndHanzi as VARCHAR) PRINT ' 数字: ' + cast( @Digit as VARCHAR) PRINT ' 数字+英文或+中文: ' + cast( @DigitAndLetterOrHanzi as varchar)
转载于:https://www.cnblogs.com/yoolonet/archive/2012/02/20/2358820.html
相关资源：数据结构—成绩单生成器