获取拼音首字母函数

最新推荐文章于 2021-04-03 13:24:25 发布

转载最新推荐文章于 2021-04-03 13:24:25 发布 · 93 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/jianglai11/archive/2008/12/10/1351636.html

本文介绍了一种在SQL中实现获取汉字拼音首字母的方法。通过创建自定义函数，可以为数据库中的中文姓名或其他字段生成拼音缩写，方便进行排序或检索。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

SQL code

--获取拼音首字母函数 create function f_GetPy(@str nvarchar(4000)) returns nvarchar(4000) as begin declare @strlen int,@re nvarchar(4000) declare @t table(chr nchar(1) collate Chinese_PRC_CI_AS,letter nchar(1)) insert into @t(chr,letter) select '吖','A' union all select '八','B' union all select '嚓','C' union all select '咑','D' union all select '妸','E' union all select '发','F' union all select '旮','G' union all select '铪','H' union all select '丌','J' union all select '咔','K' union all select '垃','L' union all select '嘸','M' union all select '拏','N' union all select '噢','O' union all select '妑','P' union all select '七','Q' union all select '呥','R' union all select '仨','S' union all select '他','T' union all select '屲','W' union all select '夕','X' union all select '丫','Y' union all select '帀','Z' select @strlen=len(@str),@re='' while @strlen>0 begin select top 1 @re=letter+@re,@strlen=@strlen-1 from @t a where chr<=substring(@str,@strlen,1) order by chr desc if @@rowcount=0 select @re=substring(@str,@strlen,1)+@re,@strlen=@strlen-1 end return(@re) end go --调用 select * from a order by case Name when '其他' then 1 else 0 end,dbo.f_GetPy(Name) --获取字符串拼音字头 --Pqs 2006-11-20 --@Char 输入的字符串 CREATE function dbo.GetPY (@Char varchar(100)) returns varchar(100) as begin --字符串长度返回数据遍历字符串位置遍历的字符 declare @i_Len int, @c_Return varchar(100), @i_pos int,@c varchar(2),@c_CN varchar(100) --初始化数据 --set @str='龙岂池中物' set @i_Len = len(@Char) set @c_Return = '' set @i_pos = 0 --遍历字符串 while (@i_pos < @i_Len ) begin set @i_pos = @i_pos + 1 set @c_CN = substring(@Char, @i_pos, 1) if (@c_CN > 'z' ) --中文处理，查询匹配 begin set @c = case when @c_CN>='帀' then 'Z' when @c_CN>='丫' and @c_CN<'帀' then 'Y' when @c_CN>='夕' and @c_CN<'丫' then 'X' when @c_CN>='屲' and @c_CN<'夕' then 'W' when @c_CN>='他' and @c_CN<'屲' then 'T' when @c_CN>='仨' and @c_CN<'他' then 'S' when @c_CN>='呥' and @c_CN<'仨' then 'R' when @c_CN>='七' and @c_CN<'呥' then 'Q' when @c_CN>='妑' and @c_CN<'七' then 'P' when @c_CN>='噢' and @c_CN<'妑' then 'O' when @c_CN>='拏' and @c_CN<'噢' then 'N' when @c_CN>='嘸' and @c_CN<'拏' then 'M' when @c_CN>='垃' and @c_CN<'嘸' then 'L' when @c_CN>='咔' and @c_CN<'垃' then 'K' when @c_CN>='丌' and @c_CN<'咔' then 'J' when @c_CN>='铪' and @c_CN<'丌' then 'H' when @c_CN>='旮' and @c_CN<'铪' then 'G' when @c_CN>='发' and @c_CN<'旮' then 'F' when @c_CN>='妸' and @c_CN<'发' then 'E' when @c_CN>='咑' and @c_CN<'妸' then 'D' when @c_CN>='嚓' and @c_CN<'咑' then 'C' when @c_CN>='八' and @c_CN<'嚓' then 'B' when @c_CN>='吖' and @c_CN<'八' then 'A' Else '' End -- select top 1 @c = Code from Code_PY where name <= @c_CN end else begin --过滤字符（除了字符、数字和'.'外的所有字符） if (@c_CN>='a' or (@c_CN>='0' and @c_CN<='9') or @c_CN='.') set @c=@c_CN else set @c='' end set @c_Return=@c_Return+isnull(@c ,'') end return upper(@c_Return) end

转载于:https://www.cnblogs.com/jianglai11/archive/2008/12/10/1351636.html