SQL Unicode

     博客好久没更新了。公司事情多，所以很多时间给用在公司的事情上了。从进公司到现在，确确实实遇到很多的问题。后期一定会和大家多多分享。
先从最近的一件事情说起，公司信息管理部门，需要我统计一份用户真实姓名（User.TrueName）的数据，按如下方式统计。

1、 中文   数量
2、 英文  数量
3、 中文+英文  数量
4、 数字  数量
5、 数字+英文或+中文  数量

   SQL Server 有通配符和正则两种方式，通配符匹配的关系一般都很简单。而正则可以解决大部分的问题。对于上面一种统计，我是按最基本的SQL写法来的。

 

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)