汉字首字母问题-数据库实现

1.数据库 函数?

?

?set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

/*
根据汉字获取全拼
1.生成所有读音临时表
2.根据Chinese_PRC_CS_AS_KS_WS 排序获取读音
*/
ALTER function [dbo].[f_GetFullPy](@str varchar(100))
returns varchar(8000)
as
begin
declare @re varchar(8000)
--生成临时表
declare @t table(chr nchar(1) collate Chinese_PRC_CS_AS_KS_WS,py nvarchar(20))
insert into @t select'吖','a'
insert into @t select'','aes'
insert into @t select'哎','ai'
insert into @t select'安','an'
insert into @t select'肮','ang'
insert into @t select'凹','ao'
insert into @t select'八','ba'
insert into @t select'','bai'
insert into @t select'','baike'
insert into @t select'','baiwa'
insert into @t select'扳','ban'
insert into @t select'邦','bang'
insert into @t select'勹','bao'
insert into @t select'','be'
insert into @t select'陂','bei'
insert into @t select'奔','ben'
insert into @t select'','beng'
insert into @t select'','bi'
insert into @t select'边','bian'
insert into @t select'','uu'
insert into @t select'灬','biao'
insert into @t select'憋','bie'
insert into @t select'','bin'
insert into @t select'冫','bing'
insert into @t select'','bo'
insert into @t select'','bu'
insert into @t select'嚓','ca'
insert into @t select'','cai'
insert into @t select'','cal'
insert into @t select'参','can'
insert into @t select'仓','cang'
insert into @t select'','cao'
insert into @t select'','ce'
insert into @t select'','cen'
insert into @t select'噌','ceng'
insert into @t select'','ceok'
insert into @t select'','ceom'
insert into @t select'','ceon'
insert into @t select'','ceor'
insert into @t select'叉','cha'
insert into @t select'','chai'
insert into @t select'','chan'
insert into @t select'伥','chang'
insert into @t select'抄','chao'
insert into @t select'车','che'
insert into @t select'抻','chen'
insert into @t select'','cheng'
insert into @t select'吃','chi'
insert into @t select'充','chong'
insert into @t select'抽','chou'
insert into @t select'出','chu'
insert into @t select'','chuai'
insert into @t select'巛','chuan'
insert into @t select'','chuang'
insert into @t select'吹','chui'
insert into @t select'','chun'
insert into @t select'踔','chuo'
insert into @t select'呲','ci'
insert into @t select'','cis'
insert into @t select'从','cong'
insert into @t select'凑','cou'
insert into @t select'粗','cu'
insert into @t select'汆','cuan'
insert into @t select'崔','cui'
insert into @t select'','cun'
insert into @t select'','cuo'
insert into @t select'撮','chua'
insert into @t select'','da'
insert into @t select'呔','dai'
insert into @t select'丹','dan'
insert into @t select'当','dang'
insert into @t select'刀','dao'
insert into @t select'','de'
insert into @t select'','dem'
insert into @t select'','den'
insert into @t select'灯','deng'
insert into @t select'','di'
insert into @t select'嗲','dia'
insert into @t select'','dian'
insert into @t select'刁','diao'
insert into @t select'爹','die'
insert into @t select'','dei'
insert into @t select'','dim'
insert into @t select'丁','ding'
insert into @t select'','diu'
insert into @t select'东','dong'
insert into @t select'','dou'
insert into @t select'','du'
insert into @t select'','duan'
insert into @t select'','dug'
insert into @t select'','dui'
insert into @t select'吨','dun'
insert into @t select'咄','duo'
insert into @t select'','e'
insert into @t select'','en'
insert into @t select'','eng'
insert into @t select'','eo'
insert into @t select'','eol'
insert into @t select'','eos'
insert into @t select'儿','er'
insert into @t select'发','fa'
insert into @t select'帆','fan'
insert into @t select'匚','fang'
insert into @t select'飞','fei'
insert into @t select'吩','fen'
insert into @t select'丰','feng'
insert into @t select'','fenwa'
insert into @t select'','fiao'
insert into @t select'','fo'
insert into @t select'','fou'
insert into @t select'夫','fu'
insert into @t select'','fui'
insert into @t select'旮','ga'
insert into @t select'','gai'
insert into @t select'甘','gan'
insert into @t select'冈','gang'
insert into @t select'皋','gao'
insert into @t select'戈','ge'
insert into @t select'给','gei'
insert into @t select'根','gen'
insert into @t select'更','geng'
insert into @t select'','geu'
insert into @t select'','gib'
insert into @t select'','go'
insert into @t select'工','gong'
insert into @t select'','gongfen'
insert into @t select'','gongli'
insert into @t select'勾','gou'
insert into @t select'估','gu'
insert into @t select'瓜','gua'
insert into @t select'乖','guai'
insert into @t select'关','guan'
insert into @t select'光','guang'
insert into @t select'归','gui'
insert into @t select'丨','gun'
insert into @t select'呙','guo'
insert into @t select'','ha'
insert into @t select'','hai'
insert into @t select'','hal'
insert into @t select'','han'
insert into @t select'','hang'
insert into @t select'','hao'
insert into @t select'','haoke'
insert into @t select'诃','he'
insert into @t select'','hei'
insert into @t select'','hen'
insert into @t select'亨','heng'
insert into @t select'','heui'
insert into @t select'','ho'
insert into @t select'','hol'
insert into @t select'','hong'
insert into @t select'','hou'
insert into @t select'乎','hu'
insert into @t select'花','hua'
insert into @t select'徊','huai'
insert into @t select'欢','huan'
insert into @t select'','huang'
insert into @t select'灰','hui'
insert into @t select'昏','hun'
insert into @t select'','huo'
insert into @t select'','geo'
insert into @t select'','hwa'
insert into @t select'丌','ji'
insert into @t select'加','jia'
insert into @t select'','jialun'
insert into @t select'戋','jian'
insert into @t select'江','jiang'
insert into @t select'艽','jiao'
insert into @t select'阶','jie'
insert into @t select'巾','jin'
insert into @t select'','jing'
insert into @t select'冂','jiong'
insert into @t select'','jiu'
insert into @t select'','jou'
insert into @t select'','ju'
insert into @t select'','juan'
insert into @t select'噘','jue'
insert into @t select'军','jun'
insert into @t select'咔','ka'
insert into @t select'开','kai'
insert into @t select'','kal'
insert into @t select'刊','kan'
insert into @t select'','hem'
insert into @t select'','kang'
insert into @t select'尻','kao'
insert into @t select'坷','ke'
insert into @t select'','ken'
insert into @t select'','keng'
insert into @t select'','keo'
insert into @t select'','keol'
insert into @t select'','keos'
insert into @t select'','keum'
insert into @t select'','ki'
insert into @t select'空','kong'
insert into @t select'','kos'
insert into @t select'抠','kou'
insert into @t select'','ku'
insert into @t select'夸','kua'
insert into @t select'蒯','kuai'
insert into @t select'宽','kuan'
insert into @t select'匡','kuang'
insert into @t select'亏','kui'
insert into @t select'坤','kun'
insert into @t select'','kuo'
insert into @t select'','kweok'
insert into @t select'垃','la'
insert into @t select'来','lai'
insert into @t select'兰','lan'
insert into @t select'啷','lang'
insert into @t select'捞','lao'
insert into @t select'仂','le'
insert into @t select'雷','lei'
insert into @t select'塄','leng'
insert into @t select'','li'
insert into @t select'俩','lia'
insert into @t select'','lian'
insert into @t select'','liang'
insert into @t select'','liao'
insert into @t select'','lie'
insert into @t select'','lin'
insert into @t select'伶','ling'
insert into @t select'溜','liu'
insert into @t select'','liwa'
insert into @t select'','lo'
insert into @t select'龙','long'
insert into @t select'娄','lou'
insert into @t select'噜','lu'
insert into @t select'驴','lv'
insert into @t select'','lue'
insert into @t select'孪','luan'
insert into @t select'','lun'
insert into @t select'','luo'
insert into @t select'','m'
insert into @t select'妈','ma'
insert into @t select'','hweong'
insert into @t select'埋','mai'
insert into @t select'颟','man'
insert into @t select'','mang'
insert into @t select'','mangmi'
insert into @t select'猫','mao'
insert into @t select'','mas'
insert into @t select'','me'
insert into @t select'','mei'
insert into @t select'','men'
insert into @t select'','meng'
insert into @t select'','meo'
insert into @t select'','mi'
insert into @t select'宀','mian'
insert into @t select'喵','miao'
insert into @t select'乜','mie'
insert into @t select'','miliklanm'
insert into @t select'民','min'
insert into @t select'','lem'
insert into @t select'名','ming'
insert into @t select'谬','miu'
insert into @t select'摸','mo'
insert into @t select'','mol'
insert into @t select'哞','mou'
insert into @t select'母','mu'
insert into @t select'','myeo'
insert into @t select'','myeon'
insert into @t select'','myeong'
insert into @t select'','na'
insert into @t select'','nai'
insert into @t select'囡','nan'
insert into @t select'囔','nang'
insert into @t select'','keg'
insert into @t select'孬','nao'
insert into @t select'疒','ne'
insert into @t select'','nei'
insert into @t select'','nem'
insert into @t select'嫩','nen'
insert into @t select'','neus'
insert into @t select'','ngag'
insert into @t select'','ngai'
insert into @t select'','ngam'
insert into @t select'妮','ni'
insert into @t select'年','nian'
insert into @t select'娘','niang'
insert into @t select'茑','niao'
insert into @t select'捏','nie'
insert into @t select'','nin'
insert into @t select'宁','ning'
insert into @t select'牛','niu'
insert into @t select'农','nong'
insert into @t select'','nou'
insert into @t select'奴','nu'
insert into @t select'女','nv'
insert into @t select'疟','nue'
insert into @t select'','nve'
insert into @t select'','nuan'
insert into @t select'','nun'
insert into @t select'','nung'
insert into @t select'挪','nuo'
insert into @t select'','o'
insert into @t select'','oes'
insert into @t select'','ol'
insert into @t select'','on'
insert into @t select'讴','ou'
insert into @t select'','pa'
insert into @t select'俳','pai'
insert into @t select'','pak'
insert into @t select'','pan'
insert into @t select'乓','pang'
insert into @t select'抛','pao'
insert into @t select'呸','pei'
insert into @t select'','pen'
insert into @t select'','peng'
insert into @t select'','peol'
insert into @t select'','phas'
insert into @t select'','phdeng'
insert into @t select'','phoi'
insert into @t select'','phos'
insert into @t select'丕','pi'
insert into @t select'','pian'
insert into @t select'缥','piao'
insert into @t select'氕','pie'
insert into @t select'丿','pianpang'
insert into @t select'姘','pin'
insert into @t select'乒','ping'
insert into @t select'钋','po'
insert into @t select'剖','pou'
insert into @t select'','deo'
insert into @t select'','ppun'
insert into @t select'仆','pu'
insert into @t select'七','qi'
insert into @t select'掐','qia'
insert into @t select'千','qian'
insert into @t select'羌','qiang'
insert into @t select'','qianke'
insert into @t select'','qianwa'
insert into @t select'悄','qiao'
insert into @t select'','qie'
insert into @t select'亲','qin'
insert into @t select'','kem'
insert into @t select'氢','qing'
insert into @t select'銎','qiong'
insert into @t select'丘','qiu'
insert into @t select'曲','qu'
insert into @t select'','keop'
insert into @t select'','quan'
insert into @t select'','que'
insert into @t select'','qun'
insert into @t select'','ra'
insert into @t select'','ram'
insert into @t select'','ran'
insert into @t select'','rang'
insert into @t select'荛','rao'
insert into @t select'惹','re'
insert into @t select'人','ren'
insert into @t select'扔','reng'
insert into @t select'日','ri'
insert into @t select'','rong'
insert into @t select'','rou'
insert into @t select'','ru'
insert into @t select'','ruan'
insert into @t select'','rui'
insert into @t select'闰','run'
insert into @t select'','ruo'
insert into @t select'仨','sa'
insert into @t select'','saeng'
insert into @t select'','sai'
insert into @t select'','sal'
insert into @t select'三','san'
insert into @t select'','sang'
insert into @t select'','sao'
insert into @t select'色','se'
insert into @t select'','sed'
insert into @t select'','sei'
insert into @t select'森','sen'
insert into @t select'','seng'
insert into @t select'','seo'
insert into @t select'','seon'
insert into @t select'杀','sha'
insert into @t select'筛','shai'
insert into @t select'山','shan'
insert into @t select'伤','shang'
insert into @t select'','shao'
insert into @t select'奢','she'
insert into @t select'申','shen'
insert into @t select'升','sheng'
insert into @t select'尸','shi'
insert into @t select'','shike'
insert into @t select'','shiwa'
insert into @t select'','shou'
insert into @t select'书','shu'
insert into @t select'刷','shua'
insert into @t select'摔','shuai'
insert into @t select'闩','shuan'
insert into @t select'双','shuang'
insert into @t select'谁','shei'
insert into @t select'','shui'
insert into @t select'吮','shun'
insert into @t select'','shuo'
insert into @t select'丝','si'
insert into @t select'','so'
insert into @t select'','sol'
insert into @t select'忪','song'
insert into @t select'','sou'
insert into @t select'苏','su'
insert into @t select'','suan'
insert into @t select'','sui'
insert into @t select'孙','sun'
insert into @t select'娑','suo'
insert into @t select'他','ta'
insert into @t select'','tae'
insert into @t select'','tai'
insert into @t select'坍','tan'
insert into @t select'铴','tang'
insert into @t select'','tao'
insert into @t select'','tap'
insert into @t select'忒','te'
insert into @t select'','teng'
insert into @t select'','teo'
insert into @t select'','teul'
insert into @t select'剔','ti'
insert into @t select'天','tian'
insert into @t select'','tiao'
insert into @t select'','tie'
insert into @t select'厅','ting'
insert into @t select'','tol'
insert into @t select'','tong'
insert into @t select'偷','tou'
insert into @t select'凸','tu'
insert into @t select'湍','tuan'
insert into @t select'推','tui'
insert into @t select'','tun'
insert into @t select'乇','tuo'
insert into @t select'','wa'
insert into @t select'歪','wai'
insert into @t select'','wan'
insert into @t select'','wang'
insert into @t select'危','wei'
insert into @t select'','wen'
insert into @t select'翁','weng'
insert into @t select'挝','wo'
insert into @t select'乌','wu'
insert into @t select'夕','xi'
insert into @t select'诶','ei'
insert into @t select'','xia'
insert into @t select'仙','xian'
insert into @t select'乡','xiang'
insert into @t select'','xiao'
insert into @t select'楔','xie'
insert into @t select'心','xin'
insert into @t select'星','xing'
insert into @t select'凶','xiong'
insert into @t select'休','xiu'
insert into @t select'','xu'
insert into @t select'','xuan'
insert into @t select'','xue'
insert into @t select'','xun'
insert into @t select'丫','ya'
insert into @t select'咽','yan'
insert into @t select'','eom'
insert into @t select'央','yang'
insert into @t select'吆','yao'
insert into @t select'椰','ye'
insert into @t select'','yen'
insert into @t select'一','yi'
insert into @t select'','i'
insert into @t select'','yin'
insert into @t select'应','ying'
insert into @t select'哟','yo'
insert into @t select'佣','yong'
insert into @t select'优','you'
insert into @t select'迂','yu'
insert into @t select'','yuan'
insert into @t select'曰','yue'
insert into @t select'','yun'
insert into @t select'','za'
insert into @t select'','zai'
insert into @t select'','zan'
insert into @t select'','zang'
insert into @t select'遭','zao'
insert into @t select'','ze'
insert into @t select'贼','zei'
insert into @t select'怎','zen'
insert into @t select'','zeng'
insert into @t select'吒','zha'
insert into @t select'','gad'
insert into @t select'','zhai'
insert into @t select'毡','zhan'
insert into @t select'张','zhang'
insert into @t select'钊','zhao'
insert into @t select'蜇','zhe'
insert into @t select'贞','zhen'
insert into @t select'','zheng'
insert into @t select'之','zhi'
insert into @t select'中','zhong'
insert into @t select'州','zhou'
insert into @t select'','zhu'
insert into @t select'抓','zhua'
insert into @t select'专','zhuan'
insert into @t select'转','zhuai'
insert into @t select'妆','zhuang'
insert into @t select'骓','zhui'
insert into @t select'','zhun'
insert into @t select'卓','zhuo'
insert into @t select'孜','zi'
insert into @t select'','zo'
insert into @t select'宗','zong'
insert into @t select'','zou'
insert into @t select'','zu'
insert into @t select'','zuan'
insert into @t select'','zui'
insert into @t select'尊','zun'
insert into @t select'昨','zuo'

declare @strlen int
select @strlen=len(@str),@re=''
while @strlen>0
begin????
????? select top 1 @re=UPPER(substring(py,1,1) )+substring(py,2,len(py))+@re,@strlen=@strlen-1
????? from @t a where chr<=substring(@str,@strlen,1)
????? order by chr collate Chinese_PRC_CS_AS_KS_WS desc
????? if @@rowcount=0
??????? select @re=substring(@str,@strlen,1)+@re,@strlen=@strlen-1
?? end
return(@re)
end

?

?

?

?

?

2.

set ANSI_NULLS ONset QUOTED_IDENTIFIER ONgo     --创建取拼音函数   ALTER FUNCTION [dbo].[f_GetPY](@str nvarchar(4000))RETURNS nvarchar(4000)ASBEGINDECLARE @py TABLE(ch char(1),hz1 nchar(1) COLLATE Chinese_PRC_CS_AS_KS_WS,hz2 nchar(1) COLLATE Chinese_PRC_CS_AS_KS_WS)INSERT @py SELECT 'A',N'吖',N'鏊'UNION  ALL SELECT 'B',N'八',N'簿'UNION  ALL SELECT 'C',N'嚓',N'错'UNION  ALL SELECT 'D',N'哒',N'跺'UNION  ALL SELECT 'E',N'屙',N'贰'UNION  ALL SELECT 'F',N'发',N'馥'UNION  ALL SELECT 'G',N'旮',N'过'UNION  ALL SELECT 'H',N'铪',N'蠖'UNION  ALL SELECT 'J',N'丌',N'竣'UNION  ALL SELECT 'K',N'咔',N'廓'UNION  ALL SELECT 'L',N'垃',N'雒'UNION  ALL SELECT 'M',N'妈',N'穆'UNION  ALL SELECT 'N',N'拿',N'糯'UNION  ALL SELECT 'O',N'噢',N'沤'UNION  ALL SELECT 'P',N'趴',N'曝'UNION  ALL SELECT 'Q',N'七',N'群'UNION  ALL SELECT 'R',N'蚺',N'箬'UNION  ALL SELECT 'S',N'仨',N'锁'UNION  ALL SELECT 'T',N'他',N'箨'UNION  ALL SELECT 'W',N'哇',N'鋈'UNION  ALL SELECT 'X',N'夕',N'蕈'UNION  ALL SELECT 'Y',N'丫',N'蕴'UNION  ALL SELECT 'Z',N'匝',N'做'DECLARE @i intSET @i=PATINDEX('%[吖-做]%' COLLATE Chinese_PRC_CS_AS_KS_WS,@str)WHILE @i>0SELECT @str=REPLACE(@str,SUBSTRING(@str,@i,1),ch),@i=PATINDEX('%[吖-做]%' COLLATE Chinese_PRC_CS_AS_KS_WS,@str)FROM @pyWHERE SUBSTRING(@str,@i,1) BETWEEN hz1 AND hz2RETURN(@str)END

?

1 楼 远去的渡口 2010-03-15 请教楼主，这些汉字与拼音的对应，如'昨','zuo' 等是自己整理的吗？还有，怎么去处理全角半角的问题呢，在文中哪里体现？
因为我曾经也研究过按笔画排序问题，与拼音排序的思想是差不多的，具体如何实现尚未实践。