在数据库中将中文转换为拼音或者汉字首字母 转
时间:2022-03-13 22:41
sql数据库
自定义一个函数
把下面代码写进去
功能是得到汉字拼音首字母
如下:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
|
create function 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 |
--函数调用实例:
select dbo.fun_getPY(‘中华人民共和国‘)
结果都为:ZHRMGHG
/* ------------------------------------------------------------- 函数: fn_GetPinyin 描述: 汉字转拼音(无数据表版) 使用: dbo.fn_GetPinyin(‘中华人民共和国‘) = zhonghuarenmingongheguo 作者: 流香羽 (改编:Tony) 博客: 流香羽 ------------------------------------------------------------- */ 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 ‘窾‘ 热门排行今日推荐热门手游 |