博客好久没更新了。公司事情多,所以很多时间给用在公司的事情上了。从进公司到现在,确确实实遇到很多的问题。后期一定会和大家多多分享。
先从最近的一件事情说起,公司信息管理部门,需要我统计一份用户真实姓名(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)
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)