比如“草类库区”，那么查询码就要为“CLKQ”
这个查询码该怎样提取？
貌似有多音字的就比较难搞了。
------解决方案--------------------
取汉字首拼音字母的，查查，有很多博客上都有。
------解决方案--------------------
- SQL code
create function [dbo].[chinese_firstletter] ( @str nvarchar(1) ) returns nvarchar(1) as begin declare @word nchar(1),@PY nvarchar(4000) set @PY='' set @word=left(@str,1) --如果非汉字字符，返回原字符 set @[email protected]+(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>[email protected] collate Chinese_PRC_CS_AS_KS_WS order by PY ASC ) else @word end) set @str=right(@str,len(@str)-1) return @PY end goSelect dbo.chinese_firstletter('福田区第五医院')drop function chinese_firstletter/* ---- F（所影响的行数为 1 行）*/]--获取拼音首字母函数gocreate function f_GetPy(@str nvarchar(4000))returns nvarchar(4000)asbegindeclare @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 @[email protected],@[email protected] from @t a where chr<=substring(@str,@strlen,1) order by chr desc if @@rowcount=0 select @re=substring(@str,@strlen,1)[email protected],@[email protected] end return(@re)endgo--调用create table tb(col varchar(20))insert into tb values('草类库区')insert into tb values('我恒大无敌')goselect 按拼音首字母 = dbo.f_GetPy(col) from tbdrop table tbdrop function f_GetPy/*按拼音首字母------------按拼音首字母CLKQWHDWD*/
------解决方案--------------------
- SQL code
---获取中文拼音函数--- create function vIFO_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 @[email protected],@[email protected] from @t a where chr <=substring(@str,@strlen,1) order by chr desc if @@rowcount=0 select @re=substring(@str,@strlen,1)[email protected],@[email protected] end return(@re) end