使用SQL 获取汉字拼音首字母

创建标量值函数

 

 1 USE [MDB]
 2 GO
 3 /****** Object:  UserDefinedFunction [dbo].[GetPY]    Script Date: 2020/6/18 14:41:11 ******/
 4 SET ANSI_NULLS ON
 5 GO
 6 SET QUOTED_IDENTIFIER ON
 7 GO
 8 
 9 --Echo_Wu 获取汉字拼音首字母
10 ALTER function [dbo].[GetPY](@str varchar(500))
11 returns varchar(500)
12 as
13 begin
14    declare @cyc int,@length int,@str1 varchar(100),@charcate varbinary(20)
15    set @cyc=1--从第几个字开始取
16    set @length=len(@str)--输入汉字的长度
17    set @str1=''--用于存放返回值
18    while @cyc<=@length
19        begin  
20           select @charcate=cast(substring(@str,@cyc,1) as varbinary)--每次取出一个字并将其转变成二进制,便于与GBK编码表进行比较
21 
22  if @charcate>=0XB0A1 and @charcate<=0XB0C4
23          set @str1=@str1+'A'--说明此汉字的首字母为A,以下同上
24     else if @charcate>=0XB0C5 and @charcate<=0XB2C0
25       set @str1=@str1+'B'
26  else if @charcate>=0XB2C1 and @charcate<=0XB4ED
27       set @str1=@str1+'C'
28  else if @charcate>=0XB4EE and @charcate<=0XB6E9
29       set @str1=@str1+'D'
30  else if @charcate>=0XB6EA and @charcate<=0XB7A1
31                        set @str1=@str1+'E'
32  else if @charcate>=0XB7A2 and @charcate<=0XB8C0
33              set @str1=@str1+'F'
34  else if @charcate>=0XB8C1 and @charcate<=0XB9FD
35                        set @str1=@str1+'G'
36  else if @charcate>=0XB9FE and @charcate<=0XBBF6
37        set @str1=@str1+'H'
38  else if @charcate>=0XBBF7 and @charcate<=0XBFA5
39        set @str1=@str1+'J'
40  else if @charcate>=0XBFA6 and @charcate<=0XC0AB
41        set @str1=@str1+'K'
42  else if @charcate>=0XC0AC and @charcate<=0XC2E7
43        set @str1=@str1+'L'
44  else if @charcate>=0XC2E8 and @charcate<=0XC4C2
45        set @str1=@str1+'M'
46  else if @charcate>=0XC4C3 and @charcate<=0XC5B5
47        set @str1=@str1+'N'
48    else if @charcate>=0XC5B6 and @charcate<=0XC5BD
49        set @str1=@str1+'O'
50  else if @charcate>=0XC5BE and @charcate<=0XC6D9
51        set @str1=@str1+'P'
52  else if @charcate>=0XC6DA and @charcate<=0XC8BA
53        set @str1=@str1+'Q'
54  else if @charcate>=0XC8BB and @charcate<=0XC8F5
55                    set @str1=@str1+'R'
56  else if @charcate>=0XC8F6 and @charcate<=0XCBF9
57        set @str1=@str1+'S'
58  else if @charcate>=0XCBFA and @charcate<=0XCDD9
59       set @str1=@str1+'T'
60  else if @charcate>=0XCDDA and @charcate<=0XCEF3
61         set @str1=@str1+'W'
62  else if @charcate>=0XCEF4 and @charcate<=0XD1B8
63         set @str1=@str1+'X'
64  else if @charcate>=0XD1B9 and @charcate<=0XD4D0
65        set @str1=@str1+'Y'
66  else if @charcate>=0XD4D1 and @charcate<=0XD7F9
67        set @str1=@str1+'Z'
68  else
69        set @str1=@str1+substring(@str,@cyc,1)--保留非汉字部分字符
70        set @cyc=@cyc+1--取出输入汉字的下一个字
71  end
72  return @str1--返回输入汉字的首字母
73  end

 

 

 

测试数据:

 

 select dbo.GetPY('中华人民共和国')

 

 


参考资料来源于:https://www.cnblogs.com/zhouhuitao/archive/2013/01/07/2849682.html

 

posted @ 2020-06-10 10:06  EnjoyToday  阅读(1608)  评论(0编辑  收藏  举报