SQL 汉字转拼音、首字母 .sql 文件

原创 2015年11月20日 10:40:00


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_procGetPY]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fn_procGetPY]
Go


-- =============================================
-- Author: Kaisen
-- Create date: 2015-11-20
-- Description:
--方法一sqlserver汉字转拼音首字母
--调用方法 select dbo.fn_procGetPY ('中國')
-- =============================================
Create FUNCTION dbo.fn_procGetPY 

@str NVARCHAR(4000) 

/*
select dbo.fn_procGetPYFirstLetter ('中國')
*/
RETURNS NVARCHAR(4000) 
--WITH ENCRYPTION 
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
 
Go






IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_procGetPinYin]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fn_procGetPinYin]
Go


-- =============================================
-- Author: Kaisen
-- Create date: 2015-11-20
-- Description:
--方法二sqlserver汉字转全拼
--调用方法 select dbo.fn_procGetPinYin ('中國')
-- =============================================
create function [dbo].fn_procGetPinYin(@str varchar(100))
returns varchar(8000)
as
begin
 declare @re varchar(8000),@crs varchar(10)
 declare @strlen int
 select @strlen=len(@str),@re=''
 while @strlen>0
 begin 
  set @crs= substring(@str,@strlen,1)
      select @re=
        case
        when @crs<'吖' then @crs
        when @crs<='厑' then 'a'
        when @crs<='靉' then 'ai'
        when @crs<='黯' then 'an'
        when @crs<='醠' then 'ang'
        when @crs<='驁' then 'ao'
        when @crs<='欛' then 'ba'
        when @crs<='瓸' then 'bai'
        when @crs<='瓣' then 'ban'
        when @crs<='鎊' then 'bang'
        when @crs<='鑤' then 'bao'
        when @crs<='鐾' then 'bei'
        when @crs<='輽' then 'ben'
        when @crs<='鏰' then 'beng'
        when @crs<='鼊' then 'bi'
        when @crs<='變' then 'bian'
        when @crs<='鰾' then 'biao'
        when @crs<='彆' then 'bie'
        when @crs<='鬢' then 'bin'
        when @crs<='靐' then 'bing'
        when @crs<='蔔' then 'bo'
        when @crs<='簿' then 'bu'
        when @crs<='囃' then 'ca'
        when @crs<='乲' then 'cai'
        when @crs<='爘' then 'can'
        when @crs<='賶' then 'cang'
        when @crs<='鼜' then 'cao'
        when @crs<='簎' then 'ce'
        when @crs<='笒' then 'cen'
        when @crs<='乽' then 'ceng'
        when @crs<='詫' then 'cha'
        when @crs<='囆' then 'chai'
        when @crs<='顫' then 'chan'
        when @crs<='韔' then 'chang'
        when @crs<='觘' then 'chao'
        when @crs<='爡' then 'che'
        when @crs<='讖' then 'chen'
        when @crs<='秤' then 'cheng'
        when @crs<='鷘' then 'chi'
        when @crs<='銃' then 'chong'
        when @crs<='殠' then 'chou'
        when @crs<='矗' then 'chu'
        when @crs<='踹' then 'chuai'
        when @crs<='鶨' then 'chuan'
        when @crs<='愴' then 'chuang'
        when @crs<='顀' then 'chui'
        when @crs<='蠢' then 'chun'
        when @crs<='縒' then 'chuo'
        when @crs<='嗭' then 'ci'
        when @crs<='謥' then 'cong'
        when @crs<='輳' then 'cou'
        when @crs<='顣' then 'cu'
        when @crs<='爨' then 'cuan'
        when @crs<='臎' then 'cui'
        when @crs<='籿' then 'cun'
        when @crs<='錯' then 'cuo'
        when @crs<='橽' then 'da'
        when @crs<='靆' then 'dai'
        when @crs<='饏' then 'dan'
        when @crs<='闣' then 'dang'
        when @crs<='纛' then 'dao'
        when @crs<='的' then 'de'
        when @crs<='扽' then 'den'
        when @crs<='鐙' then 'deng'
        when @crs<='螮' then 'di'
        when @crs<='嗲' then 'dia'
        when @crs<='驔' then 'dian'
        when @crs<='鑃' then 'diao'
        when @crs<='嚸' then 'die'
        when @crs<='顁' then 'ding'
        when @crs<='銩' then 'diu'
        when @crs<='霘' then 'dong'
        when @crs<='鬭' then 'dou'
        when @crs<='蠹' then 'du'
        when @crs<='叾' then 'duan'
        when @crs<='譵' then 'dui'
        when @crs<='踲' then 'dun'
        when @crs<='鵽' then 'duo'
        when @crs<='鱷' then 'e'
        when @crs<='摁' then 'en'
        when @crs<='鞥' then 'eng'
        when @crs<='樲' then 'er'
        when @crs<='髮' then 'fa'
        when @crs<='瀪' then 'fan'
        when @crs<='放' then 'fang'
        when @crs<='靅' then 'fei'
        when @crs<='鱝' then 'fen'
        when @crs<='覅' then 'feng'
        when @crs<='梻' then 'fo'
        when @crs<='鴀' then 'fou'
        when @crs<='猤' then 'fu'
        when @crs<='魀' then 'ga'
        when @crs<='瓂' then 'gai'
        when @crs<='灨' then 'gan'
        when @crs<='戇' then 'gang'
        when @crs<='鋯' then 'gao'
        when @crs<='獦' then 'ge'
        when @crs<='給' then 'gei'
        when @crs<='搄' then 'gen'
        when @crs<='堩' then 'geng'
        when @crs<='兣' then 'gong'
        when @crs<='購' then 'gou'
        when @crs<='顧' then 'gu'
        when @crs<='詿' then 'gua'
        when @crs<='恠' then 'guai'
        when @crs<='鱹' then 'guan'
        when @crs<='撗' then 'guang'
        when @crs<='鱥' then 'gui'
        when @crs<='謴' then 'gun'
        when @crs<='腂' then 'guo'
        when @crs<='哈' then 'ha'
        when @crs<='饚' then 'hai'
        when @crs<='鶾' then 'han'
        when @crs<='沆' then 'hang'
        when @crs<='兞' then 'hao'
        when @crs<='靏' then 'he'
        when @crs<='嬒' then 'hei'
        when @crs<='恨' then 'hen'
        when @crs<='堼' then 'heng'
        when @crs<='鬨' then 'hong'
        when @crs<='鱟' then 'hou'
        when @crs<='鸌' then 'hu'
        when @crs<='蘳' then 'hua'
        when @crs<='蘾' then 'huai'
        when @crs<='鰀' then 'huan'
        when @crs<='鎤' then 'huang'
        when @crs<='顪' then 'hui'
        when @crs<='諢' then 'hun'
        when @crs<='夻' then 'huo'
        when @crs<='驥' then 'ji'
        when @crs<='嗧' then 'jia'
        when @crs<='鑳' then 'jian'
        when @crs<='謽' then 'jiang'
        when @crs<='釂' then 'jiao'
        when @crs<='繲' then 'jie'
        when @crs<='齽' then 'jin'
        when @crs<='竸' then 'jing'
        when @crs<='蘔' then 'jiong'
        when @crs<='欍' then 'jiu'
        when @crs<='爠' then 'ju'
        when @crs<='羂' then 'juan'
        when @crs<='钁' then 'jue'
        when @crs<='攈' then 'jun'
        when @crs<='鉲' then 'ka'
        when @crs<='乫' then 'kai'
        when @crs<='矙' then 'kan'
        when @crs<='閌' then 'kang'
        when @crs<='鯌' then 'kao'
        when @crs<='騍' then 'ke'
        when @crs<='褃' then 'ken'
        when @crs<='鏗' then 'keng'
        when @crs<='廤' then 'kong'
        when @crs<='鷇' then 'kou'
        when @crs<='嚳' then 'ku'
        when @crs<='骻' then 'kua'
        when @crs<='鱠' then 'kuai'
        when @crs<='窾' then 'kuan'
        when @crs<='鑛' then 'kuang'
        when @crs<='鑎' then 'kui'
        when @crs<='睏' then 'kun'
        when @crs<='穒' then 'kuo'
        when @crs<='鞡' then 'la'
        when @crs<='籟' then 'lai'
        when @crs<='糷' then 'lan'
        when @crs<='唥' then 'lang'
        when @crs<='軂' then 'lao'
        when @crs<='餎' then 'le'
        when @crs<='脷' then 'lei'
        when @crs<='睖' then 'leng'
        when @crs<='瓈' then 'li'
        when @crs<='倆' then 'lia'
        when @crs<='纞' then 'lian'
        when @crs<='鍄' then 'liang'
        when @crs<='瞭' then 'liao'
        when @crs<='鱲' then 'lie'
        when @crs<='轥' then 'lin'
        when @crs<='炩' then 'ling'
        when @crs<='咯' then 'liu'
        when @crs<='贚' then 'long'
        when @crs<='鏤' then 'lou'
        when @crs<='氇' then 'lu'
        when @crs<='鑢' then 'lv'
        when @crs<='亂' then 'luan'
        when @crs<='擽' then 'lue'
        when @crs<='論' then 'lun'
        when @crs<='鱳' then 'luo'
        when @crs<='嘛' then 'ma'
        when @crs<='霢' then 'mai'
        when @crs<='蘰' then 'man'
        when @crs<='蠎' then 'mang'
        when @crs<='唜' then 'mao'
        when @crs<='癦' then 'me'
        when @crs<='嚜' then 'mei'
        when @crs<='們' then 'men'
        when @crs<='霥' then 'meng'
        when @crs<='羃' then 'mi'
        when @crs<='麵' then 'mian'
        when @crs<='廟' then 'miao'
        when @crs<='鱴' then 'mie'
        when @crs<='鰵' then 'min'
        when @crs<='詺' then 'ming'
        when @crs<='謬' then 'miu'
        when @crs<='耱' then 'mo'
        when @crs<='麰' then 'mou'
        when @crs<='旀' then 'mu'
        when @crs<='魶' then 'na'
        when @crs<='錼' then 'nai'
        when @crs<='婻' then 'nan'
        when @crs<='齉' then 'nang'
        when @crs<='臑' then 'nao'
        when @crs<='呢' then 'ne'
        when @crs<='焾' then 'nei'
        when @crs<='嫩' then 'nen'
        when @crs<='能' then 'neng'
        when @crs<='嬺' then 'ni'
        when @crs<='艌' then 'nian'
        when @crs<='釀' then 'niang'
        when @crs<='脲' then 'niao'
        when @crs<='钀' then 'nie'
        when @crs<='拰' then 'nin'
        when @crs<='濘' then 'ning'
        when @crs<='靵' then 'niu'
        when @crs<='齈' then 'nong'
        when @crs<='譳' then 'nou'
        when @crs<='搙' then 'nu'
        when @crs<='衄' then 'nv'
        when @crs<='瘧' then 'nue'
        when @crs<='燶' then 'nuan'
        when @crs<='桛' then 'nuo'
        when @crs<='鞰' then 'o'
        when @crs<='漚' then 'ou'
        when @crs<='袙' then 'pa'
        when @crs<='磗' then 'pai'
        when @crs<='鑻' then 'pan'
        when @crs<='胖' then 'pang'
        when @crs<='礮' then 'pao'
        when @crs<='轡' then 'pei'
        when @crs<='喯' then 'pen'
        when @crs<='喸' then 'peng'
        when @crs<='鸊' then 'pi'
        when @crs<='騙' then 'pian'
        when @crs<='慓' then 'piao'
        when @crs<='嫳' then 'pie'
        when @crs<='聘' then 'pin'
        when @crs<='蘋' then 'ping'
        when @crs<='魄' then 'po'
        when @crs<='哛' then 'pou'
        when @crs<='曝' then 'pu'
        when @crs<='蟿' then 'qi'
        when @crs<='髂' then 'qia'
        when @crs<='縴' then 'qian'
        when @crs<='瓩' then 'qiang'
        when @crs<='躈' then 'qiao'
        when @crs<='籡' then 'qie'
        when @crs<='藽' then 'qin'
        when @crs<='櫦' then 'qing'
        when @crs<='瓗' then 'qiong'
        when @crs<='糗' then 'qiu'
        when @crs<='覻' then 'qu'
        when @crs<='勸' then 'quan'
        when @crs<='礭' then 'que'
        when @crs<='囕' then 'qun'
        when @crs<='橪' then 'ran'
        when @crs<='讓' then 'rang'
        when @crs<='繞' then 'rao'
        when @crs<='熱' then 're'
        when @crs<='餁' then 'ren'
        when @crs<='陾' then 'reng'
        when @crs<='馹' then 'ri'
        when @crs<='穃' then 'rong'
        when @crs<='嶿' then 'rou'
        when @crs<='擩' then 'ru'
        when @crs<='礝' then 'ruan'
        when @crs<='壡' then 'rui'
        when @crs<='橍' then 'run'
        when @crs<='鶸' then 'ruo'
        when @crs<='栍' then 'sa'
        when @crs<='虄' then 'sai'
        when @crs<='閐' then 'san'
        when @crs<='喪' then 'sang'
        when @crs<='髞' then 'sao'
        when @crs<='飋' then 'se'
        when @crs<='篸' then 'sen'
        when @crs<='縇' then 'seng'
        when @crs<='霎' then 'sha'
        when @crs<='曬' then 'shai'
        when @crs<='鱔' then 'shan'
        when @crs<='緔' then 'shang'
        when @crs<='潲' then 'shao'
        when @crs<='欇' then 'she'
        when @crs<='瘮' then 'shen'
        when @crs<='賸' then 'sheng'
        when @crs<='瓧' then 'shi'
        when @crs<='鏉' then 'shou'
        when @crs<='虪' then 'shu'
        when @crs<='誜' then 'shua'
        when @crs<='卛' then 'shuai'
        when @crs<='腨' then 'shuan'
        when @crs<='灀' then 'shuang'
        when @crs<='睡' then 'shui'
        when @crs<='鬊' then 'shun'
        when @crs<='鑠' then 'shuo'
        when @crs<='乺' then 'si'
        when @crs<='鎹' then 'song'
        when @crs<='瘶' then 'sou'
        when @crs<='鷫' then 'su'
        when @crs<='算' then 'suan'
        when @crs<='鐩' then 'sui'
        when @crs<='潠' then 'sun'
        when @crs<='蜶' then 'suo'
        when @crs<='襨' then 'ta'
        when @crs<='燤' then 'tai'
        when @crs<='賧' then 'tan'
        when @crs<='燙' then 'tang'
        when @crs<='畓' then 'tao'
        when @crs<='蟘' then 'te'
        when @crs<='朰' then 'teng'
        when @crs<='趯' then 'ti'
        when @crs<='舚' then 'tian'
        when @crs<='糶' then 'tiao'
        when @crs<='餮' then 'tie'
        when @crs<='乭' then 'ting'
        when @crs<='憅' then 'tong'
        when @crs<='透' then 'tou'
        when @crs<='鵵' then 'tu'
        when @crs<='褖' then 'tuan'
        when @crs<='駾' then 'tui'
        when @crs<='坉' then 'tun'
        when @crs<='籜' then 'tuo'
        when @crs<='韤' then 'wa'
        when @crs<='顡' then 'wai'
        when @crs<='贎' then 'wan'
        when @crs<='朢' then 'wang'
        when @crs<='躛' then 'wei'
        when @crs<='璺' then 'wen'
        when @crs<='齆' then 'weng'
        when @crs<='齷' then 'wo'
        when @crs<='鶩' then 'wu'
        when @crs<='衋' then 'xi'
        when @crs<='鏬' then 'xia'
        when @crs<='鼸' then 'xian'
        when @crs<='鱌' then 'xiang'
        when @crs<='斆' then 'xiao'
        when @crs<='躞' then 'xie'
        when @crs<='釁' then 'xin'
        when @crs<='臖' then 'xing'
        when @crs<='敻' then 'xiong'
        when @crs<='齅' then 'xiu'
        when @crs<='蓿' then 'xu'
        when @crs<='贙' then 'xuan'
        when @crs<='瀥' then 'xue'
        when @crs<='鑂' then 'xun'
        when @crs<='齾' then 'ya'
        when @crs<='灩' then 'yan'
        when @crs<='樣' then 'yang'
        when @crs<='鑰' then 'yao'
        when @crs<='岃' then 'ye'
        when @crs<='齸' then 'yi'
        when @crs<='檼' then 'yin'
        when @crs<='譍' then 'ying'
        when @crs<='喲' then 'yo'
        when @crs<='醟' then 'yong'
        when @crs<='鼬' then 'you'
        when @crs<='爩' then 'yu'
        when @crs<='願' then 'yuan'
        when @crs<='鸙' then 'yue'
        when @crs<='韻' then 'yun'
        when @crs<='雥' then 'za'
        when @crs<='縡' then 'zai'
        when @crs<='饡' then 'zan'
        when @crs<='臟' then 'zang'
        when @crs<='竈' then 'zao'
        when @crs<='稄' then 'ze'
        when @crs<='鱡' then 'zei'
        when @crs<='囎' then 'zen'
        when @crs<='贈' then 'zeng'
        when @crs<='醡' then 'zha'
        when @crs<='瘵' then 'zhai'
        when @crs<='驏' then 'zhan'
        when @crs<='瞕' then 'zhang'
        when @crs<='羄' then 'zhao'
        when @crs<='鷓' then 'zhe'
        when @crs<='黮' then 'zhen'
        when @crs<='證' then 'zheng'
        when @crs<='豒' then 'zhi'
        when @crs<='諥' then 'zhong'
        when @crs<='驟' then 'zhou'
        when @crs<='鑄' then 'zhu'
        when @crs<='爪' then 'zhua'
        when @crs<='跩' then 'zhuai'
        when @crs<='籑' then 'zhuan'
        when @crs<='戅' then 'zhuang'
        when @crs<='鑆' then 'zhui'
        when @crs<='稕' then 'zhun'
        when @crs<='籱' then 'zhuo'
        when @crs<='漬' then 'zi'
        when @crs<='縱' then 'zong'
        when @crs<='媰' then 'zou'
        when @crs<='謯' then 'zu'
        when @crs<='攥' then 'zuan'
        when @crs<='欈' then 'zui'
        when @crs<='銌' then 'zun'
        when @crs<='咗' then 'zuo'
        --else  @crs end+' '+@re,@strlen=@strlen-1 
        --去掉拼音之间的间隔
        else  @crs end+''+@re,@strlen=@strlen-1 
   end
 return(@re)
end
go

sql 中文转拼音首字母

--可支持大字符集20000个汉字! create function f_ch2py(@chn nchar(1)) returns char(1) as begin declare @n int d...
  • zhanglong_longlong
  • zhanglong_longlong
  • 2015年07月06日 11:41
  • 792

SQLServer汉字转全拼音函数

最近用到项目,当输入错别字时也需要匹配,直接网上找到一篇直接改写加了拼音排序,让语句更通用。拿来主义USE Test go IF OBJECT_ID('Fn_GetQuanPin','Fn') IS...
  • roy_88
  • roy_88
  • 2013年04月08日 15:15
  • 7330

根据汉字获取它的字符串拼音首字母(大写),含多音字

/// /// 根据汉字获取它的字符串拼音首字母(大写),含多音字 /// /// /// publi...
  • zhao19890429
  • zhao19890429
  • 2013年12月08日 10:34
  • 3825

数据查询支持中文拼音首字母模糊检索

需求:在做软件的过程中,发现不少客户在做数据检索时,打汉字怕麻烦,也影响工作效率,要求在查询时,支持查询汉字的字母开头查询。     做到支持数据库拼音码查询,主要分一下几步实现:     1...
  • smartsmile2012
  • smartsmile2012
  • 2013年01月10日 10:11
  • 6797

完善C#汉字转拼音首字母代码

最近写一个小的程序,需要用到根据汉字自动生成拼音首字母的代码,从网上搜了好多地方都大概找到了下面的代码。但代码存在问题如下:有些常用的汉字无法识别(例如汉字琦、闫、岚等等),包括全椒的括号等特殊字符。...
  • guijianchouperfect
  • guijianchouperfect
  • 2017年06月06日 22:10
  • 909

中文汉字转拼音——大写(默认),小写,首字母大写

汉字转拼音
  • pds574834424
  • pds574834424
  • 2015年04月16日 18:32
  • 1445

SQLServer汉字转全拼音函数

原文: http://blog.csdn.net/roy_88/article/details/8772631 最近用到项目,当输入错别字时也需要匹配,直接网上找到一篇直接改写加了拼音排序,...
  • yenange
  • yenange
  • 2017年04月17日 09:53
  • 346

sql 汉字按照首字母排序博客分类:

SQL mysql   我们的MySQL使用latin1的默认字符集,也就是说,对汉字字段直接使用GBK内码的编码进行存储,当需要对一些有汉字的字段进行拼音排序时(特别涉及到类似于名字这样的字段时...
  • ailsaluo
  • ailsaluo
  • 2014年06月17日 17:23
  • 2791

oracle 提取中文字符串拼音首字母函数,拼音简码提取函数

从别的数据库里拿到的获取拼音简码的函数,觉得挺有意思的,具体的思路就是,通过oracle的NLSSORT函数对汉字按照拼音排序,然后根据汉字的区间返回对应的首字母。具体实现效果和代码如下。...
  • Ezitai
  • Ezitai
  • 2017年03月31日 11:55
  • 1694

VC/MFC 获取汉字拼音首字母(unicode编码)

VC 获取汉字拼音首字母(unicode编码)上CString GetFirstLetter(LPCTSTR strName) { static int li_SecPosValue[] = ...
  • china200_ok
  • china200_ok
  • 2016年01月08日 23:33
  • 1658
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:SQL 汉字转拼音、首字母 .sql 文件
举报原因:
原因补充:

(最多只允许输入30个字)