SQL多音词汉字转拼音
遇到一个需求 取字符串的首字母 汉字则取其拼音
之前在网上搜索到了转换函数 但对于多音词的处理未能实现
这儿贴出 来自 CSDN中在
# maco_wang
# (☆叶子☆)
空间找到的函数
求能解决多音词的函数 单个多音字暂时不考虑
- SQL code
--创建函数IF OBJECT_ID('[fn_GetPinyin]') IS NOT NULL DROP FUNCTION [fn_GetPinyin] GO create function [dbo].[fn_GetPinyin](@words nvarchar(2000)) returns varchar(8000) as begin declare @word nchar(1) declare @pinyin varchar(8000) declare @i int declare @words_len int declare @unicode int set @i = 1 set @words = ltrim(rtrim(@words)) set @words_len = len(@words) while (@i <= @words_len) --循环取字符 begin set @word = substring(@words, @i, 1) set @unicode = unicode(@word) set @pinyin = ISNULL(@pinyin +SPACE(1),'')+ (case when unicode(@word) between 19968 and 19968+20901 then (select top 1 py from ( select 'a' as py,N'' as word union all select 'ai',N'' union all select 'an',N'黯' union all select 'ang',N'' union all select 'ao',N'' union all select 'ba',N'' union all select 'bai',N'' -- union all select 'ban',N'瓣' union all select 'bang',N'' union all select 'bao',N'' union all select 'bei',N'鐾' union all select 'ben',N'' union all select 'beng',N'' union all select 'bi',N'' union all select 'bian',N'' union all select 'biao',N'' union all select 'bie',N'' union all select 'bin',N'' union all select 'bing',N'' union all select 'bo',N'' union all select 'bu',N'簿' union all select 'ca',N'' union all select 'cai',N'' -- union all select 'can',N'' union all select 'cang',N'' union all select 'cao',N'' union all select 'ce',N'' union all select 'cen',N'' union all select 'ceng',N'' -- union all select 'cha',N'' union all select 'chai',N'' union all select 'chan',N'' union all select 'chang',N'' union all select 'chao',N'' union all select 'che',N'' union all select 'chen',N'' union all select 'cheng',N'秤' union all select 'chi',N'' union all select 'chong',N'' union all select 'chou',N'' union all select 'chu',N'矗' union all select 'chuai',N'踹' union all select 'chuan',N'' union all select 'chuang',N'' union all select 'chui',N'' union all select 'chun',N'蠢' union all select 'chuo',N'' union all select 'ci',N'' -- union all select 'cong',N'' union all select 'cou',N'' union all select 'cu',N'' union all select 'cuan',N'爨' union all select 'cui',N'' union all select 'cun',N'' union all select 'cuo',N'' union all select 'da',N'' union all select 'dai',N'' union all select 'dan',N'' union all select 'dang',N'' union all select 'dao',N'纛' union all select 'de',N'的' union all select 'den',N'' union all select 'deng',N'' union all select 'di',N'' union all select 'dia',N'嗲' union all select 'dian',N'' union all select 'diao',N'' union all select 'die',N'' -- union all select 'ding',N'' union all select 'diu',N'' union all select 'dong',N'' union all select 'dou',N'' union all select 'du',N'蠹' union all select 'duan',N'' -- union all select 'dui',N'' union all select 'dun',N'' union all select 'duo',N'' union all select 'e',N'' union all select 'en',N'摁' union all select 'eng',N'' union all select 'er',N'' union all select 'fa',N'' union all select 'fan',N'' union all select 'fang',N'放' union all select 'fei',N'' union all select 'fen',N'' union all select 'feng',N'' union all select 'fo',N'' union all select 'fou',N'' union all select 'fu',N'' -- union all select 'ga',N'' union all select 'gai',N'' union all select 'gan',N'' union all select 'gang',N'' union all select 'gao',N'' union all select 'ge',N'' union all select 'gei',N'' union all select 'gen',N'' union all select 'geng',N'' -- union all select 'gong',N'' -- union all select 'gou',N'' union all select 'gu',N'' union all select 'gua',N'' union all select 'guai',N'' union all select 'guan',N'' union all select 'guang',N'' union all select 'gui',N'' union all select 'gun',N'' union all select 'guo',N'' union all select 'ha',N'哈' union all select 'hai',N'' union all select 'han',N'' union all select 'hang',N'沆' union all select 'hao',N'' union all select 'he',N'' union all select 'hei',N'' union all select 'hen',N'恨' union all select 'heng',N'' -- union all select 'hong',N'' union all select 'hou',N'' union all select 'hu',N'' union all select 'hua',N'' union all select 'huai',N'' union all select 'huan',N'' union all select 'huang',N'' union all select 'hui',N'' union all select 'hun',N'' union all select 'huo',N'' union all select 'ji',N'' union all select 'jia',N'' union all select 'jian',N'' union all select 'jiang',N'' union all select 'jiao',N'' union all select 'jie',N'' union all select 'jin',N'' union all select 'jing',N'' union all select 'jiong',N'' union all select 'jiu',N'' union all select 'ju',N'' union all select 'juan',N'' union all select 'jue',N'' union all select 'jun',N'' union all select 'ka',N'' union all select 'kai',N'' -- union all select 'kan',N'' union all select 'kang',N'' union all select 'kao',N'' union all select 'ke',N'' union all select 'ken',N'' union all select 'keng',N'' -- union all select 'kong',N'' union all select 'kou',N'' union all select 'ku',N'' union all select 'kua',N'' union all select 'kuai',N'' union all select 'kuan',N'' union all select 'kuang',N'' union all select 'kui',N'' union all select 'kun',N'' union all select 'kuo',N'' union all select 'la',N'' union all select 'lai',N'' union all select 'lan',N'' union all select 'lang',N'' union all select 'lao',N'' union all select 'le',N'' union all select 'lei',N'' --嘞 union all select 'leng',N'' union all select 'li',N'' union all select 'lia',N'' union all select 'lian',N'' union all select 'liang',N'' union all select 'liao',N'' union all select 'lie',N'' union all select 'lin',N'' --拎 union all select 'ling',N'' union all select 'liu',N'咯' --咯 union all select 'long',N'' union all select 'lou',N'' union all select 'lu',N'氇'
[解决办法]
sql的不会，要不整到程序中做？
http://blog.csdn.net/net_lover/article/details/4095606
[解决办法]
从拼音输入法码表文件中提取数据库，然后再查询。
[解决办法]
还是需要词库，在sql就比较繁琐了。在代码完成也许更简洁，1楼的不错
[解决办法]
[解决办法]
正好SQL中写了一个FUNCTION
GO
--取出汉字的拼音首码
--入参：汉字字符串
--出参：汉字字符串首拼音码
CREATE function [dbo].[Fun_GetPY]
(
@str nvarchar(4000)
)
returns nvarchar(4000)
as
begin
declare @word nchar(1),@PY nvarchar(4000)
set @PY=''
while len(@str)>0
begin
set @word=left(@str,1)
--如果非汉字字符，返回原字符
set @PY=@PY+(case when unicode(@word) between 19968 and 19968+20901
then (
select top 1 PY
from
(
select 'A' as PY,N'' as word
union all select 'B',N'簿'
union all select 'C',N''
union all select 'D',N''
union all select 'E',N''
union all select 'F',N''
union all select 'G',N''
union all select 'H',N''
union all select 'J',N''
union all select 'K',N''
union all select 'L',N''
union all select 'M',N''
union all select 'N',N''
union all select 'O',N''
union all select 'P',N'曝'
union all select 'Q',N''
union all select 'R',N''
union all select 'S',N''
union all select 'T',N''
union all select 'W',N''
union all select 'X',N''
union all select 'Y',N''
union all select 'Z',N''
) T
where word>=@word collate Chinese_PRC_CS_AS_KS_WS
order by PY ASC
)
else @word
end)
set @str=right(@str,len(@str)-1)
end
return @PY
end
[解决办法]
http://www.cnblogs.com/ret00100/archive/2010/08/06/1793725.html
[解决办法]
还是建立一个字段,专门用来存储拼音吧,把基本的多音字搞一个字库表,碰到包含的,保存进数据库时,提示手工确认一下,以后根据这个来匹配