SQL从中文中获取拼音首字母

今天做开发，客户要求，输入拼音首字母，就能获取表中所有客户名称。

 

步骤：在数据库上造个函数，然后调用这个函数即可

 

1 select dbo.fnpbGetPYFirstLetter(FName) as chinaname,FName from t_Organization
2 
3 --fnpbGetPYFirstLetter()    函数名
4 --FName        参数
 1 --全部扔进去执行，不用修改
 2 Create FUNCTION dbo.fnpbGetPYFirstLetter   
 3 (   
 4 @str NVARCHAR(4000)   
 5 )   
 6 /*  
 7 select dbo. fnpbGetPYFirstLetter ('中國香港')  
 8 */  
 9 RETURNS NVARCHAR(4000)   
10 --WITH ENCRYPTION   
11 AS   
12 BEGIN   
13 DECLARE @WORD NCHAR(1),@PY NVARCHAR(4000)   
14   
15 SET @PY=''   
16   
17 WHILE LEN(@STR)>0   
18 BEGIN   
19 SET @WORD=LEFT(@STR,1)   
20   
21 --如果非漢字字符﹐返回原字符   
22 SET @PY=@PY+(CASE WHEN UNICODE(@WORD) BETWEEN 19968 AND 19968+20901   
23 THEN (   
24 SELECT TOP 1 PY   
25 FROM   
26 (   
27 SELECT 'A' AS PY,N'驁' AS WORD   
28 UNION ALL SELECT 'B',N'簿'   
29 UNION ALL SELECT 'C',N'錯'   
30 UNION ALL SELECT 'D',N'鵽'   
31 UNION ALL SELECT 'E',N'樲'   
32 UNION ALL SELECT 'F',N'鰒'   
33 UNION ALL SELECT 'G',N'腂'   
34 UNION ALL SELECT 'H',N'夻'   
35 UNION ALL SELECT 'J',N'攈'   
36 UNION ALL SELECT 'K',N'穒'   
37 UNION ALL SELECT 'L',N'鱳'   
38 UNION ALL SELECT 'M',N'旀'   
39 UNION ALL SELECT 'N',N'桛'   
40 UNION ALL SELECT 'O',N'漚'   
41 UNION ALL SELECT 'P',N'曝'   
42 UNION ALL SELECT 'Q',N'囕'   
43 UNION ALL SELECT 'R',N'鶸'   
44 UNION ALL SELECT 'S',N'蜶'   
45 UNION ALL SELECT 'T',N'籜'   
46 UNION ALL SELECT 'W',N'鶩'   
47 UNION ALL SELECT 'X',N'鑂'   
48 UNION ALL SELECT 'Y',N'韻'   
49 UNION ALL SELECT 'Z',N'做'   
50 ) T   
51 WHERE WORD>=@WORD COLLATE CHINESE_PRC_CS_AS_KS_WS   
52 ORDER BY PY ASC   
53 )   
54 ELSE @WORD   
55 END)   
56 SET @STR=RIGHT(@STR,LEN(@STR)-1)   
57 END   
58   
59 RETURN @PY   
60   
61 END  
62   
63 Go
View Code

 

posted @ 2017-05-26 17:03  唐尧  阅读(3888)  评论(0)  编辑  收藏  举报