34,838
社区成员




USE [LzmtwWorks]
GO
-- ===============================================================
-- Author:LzmTW
-- Create date:20080123
-- Description:根据中文名字创建用户列表。
-- @UserName:中文名字。两到四个汉字组成,否则视为无效。
-- ===============================================================
CREATE FUNCTION [Helper].[UserIDList]
(
@UserName nvarchar(20)
)
RETURNS
@Table TABLE
(
[ID] tinyint identity(1,1) primary key
,[UserID] varchar(200)
)
AS
BEGIN
IF @UserName IS NULL
RETURN
SET @UserName = REPLACE(@UserName, N' ', N'')
DECLARE
@Count tinyint
SET @Count = LEN(@UserName)
IF NOT @Count BETWEEN 2 AND 4
RETURN
DECLARE @Table0 TABLE
(
[ID] int PRIMARY KEY
,[Char] nchar(1)
,[Unicode] int
)
DECLARE
@TmpCount tinyint
,@xml xml
SET @xml = [Chars].[xml](@UserName)
INSERT INTO @Table0
SELECT *
FROM [Chars].[xmlTable](@xml)
SELECT @TmpCOunt = COUNT(*) FROM @Table0 WHERE [Unicode] > 12294
IF @Count <> @TmpCount
RETURN
DECLARE @Table1 TABLE([ID] tinyint primary key, [V1] nvarchar(20), [V2] nvarchar(20), [V3] nvarchar(20), [V4] nvarchar(20))
INSERT @Table1([ID]) VALUES(1)
INSERT @Table1([ID]) VALUES(2)
INSERT @Table1([ID]) VALUES(3)
INSERT @Table1([ID]) VALUES(4)
DECLARE
@IsValid bit
,@Index tinyint
,@Char nchar(1)
,@CharPinyin varchar(10)
,@Vowel varchar(2)
SET @IsValid = 1
SET @Index = 1
WHILE @Index <= @Count
BEGIN
SELECT @Char = [Char] FROM @Table0 WHERE [ID] = @Index
SELECT @CharPinyin = [Pinyin] FROM [Chinese].[CharPinyin](@Char)
IF @CharPinyin IS NULL
BEGIN
SET @IsValid = 0
GOTO NOTVALID
END
SET @Vowel = CASE WHEN SUBSTRING(@CharPinyin, 2, 1) = 'h' THEN LEFT(@CharPinyin, 2) ELSE LEFT(@CharPinyin, 1) END
IF @Index = 1
BEGIN
UPDATE @Table1
SET [V1] = @CharPinyin
WHERE [ID] = 1
UPDATE @Table1
SET [V1] = @Vowel
WHERE [ID] = 2
END
IF @Index = 2
BEGIN
UPDATE @Table1
SET [V2] = @CharPinyin
WHERE [ID] = 1
UPDATE @Table1
SET [V2] = N'_' + @CharPinyin
WHERE [ID] = 2
UPDATE @Table1
SET [V2] = @Vowel
WHERE [ID] = 3
UPDATE @Table1
SET [V2] = N'_' + @Vowel
WHERE [ID] = 4
END
IF @Index = 3
BEGIN
UPDATE @Table1
SET [V3] = @CharPinyin
WHERE [ID] = 1
UPDATE @Table1
SET [V3] = N'_' + @CharPinyin
WHERE [ID] = 2
UPDATE @Table1
SET [V3] = @Vowel
WHERE [ID] = 3
UPDATE @Table1
SET [V3] = N'_' + @Vowel
WHERE [ID] = 4
END
IF @Index = 4
BEGIN
UPDATE @Table1
SET [V4] = @CharPinyin
WHERE [ID] = 1
UPDATE @Table1
SET [V4] = N'_' + @CharPinyin
WHERE [ID] = 2
UPDATE @Table1
SET [V4] = @Vowel
WHERE [ID] = 3
UPDATE @Table1
SET [V4] = N'_' + @Vowel
WHERE [ID] = 4
END
SET @Index = @Index + 1
END
NOTVALID:
IF @IsValid = 0
RETURN
DECLARE @Table2 TABLE (UserID varchar(50) primary key, Num tinyint)
DECLARE @Table3 TABLE (UserID varchar(50) ,Num tinyint)
INSERT INTO @Table3
SELECT DISTINCT
UserID = a.[V1] + b.[V2] + ISNULL(c.[V3], '') + ISNULL(d.[V4], '')
,Num = a.[ID] + b.[ID] + ISNULL(c.[ID], 0) + ISNULL(d.[ID], 0)
FROM @Table1 a CROSS JOIN @Table1 b CROSS JOIN @Table1 c CROSS JOIN @Table1 d
WHERE NOT a.[V1] IS NULL
INSERT INTO @Table2(UserID)
SELECT DISTINCT UserID FROM @Table3
UPDATE @Table2
SET Num = b.Num
FROM @Table2 a
INNER JOIN
(
SELECT a.UserID, MIN(a.Num) Num FROM @Table3 a INNER JOIN @Table2 b ON a.UserID = b.UserID GROUP BY a.[UserID]
) b
ON a.UserID = b.UserID
INSERT INTO @Table
SELECT UserID
FROM @Table2
ORDER BY num, SOUNDEX (UserID) DESC, LEN(UserID) DESC
RETURN
END
DECLARE @UserName nvarchar(20)
SET @userName = N'张小明'
SELECT * FROM [LzmtwWorks].[Helper].[UserIDList] (@UserName)
ID UserID
---- --------------------------
1 zhangxiaoming
2 zhangxiao_ming
3 zhang_xiaoming
4 zhxiaoming
5 zhang_xiao_ming
6 zh_xiaoming
7 zhxiao_ming
8 zhangxiaom
9 zhangxming
10 zh_xiao_ming
11 zhang_xiaom
12 zhang_xming
13 zhangx_ming
14 zhangxiao_m
15 zhxiaom
16 zhxming
17 zhang_x_ming
18 zhang_xiao_m
19 zh_xiaom
20 zh_xming
21 zhx_ming
22 zhxiao_m
23 zhangxm
24 zh_x_ming
25 zh_xiao_m
26 zhangx_m
27 zhang_xm
28 zhxm
29 zhang_x_m
30 zh_xm
31 zhx_m
32 zh_x_m
(32 行受影响)