首页 诗词 字典 板报 句子 名言 友答 励志 学校 网站地图
当前位置: 首页 > 教程频道 > 数据库 > SQL Server >

怎样快速取查询码?解决办法

2012-04-19 
怎样快速取查询码?比如“草类库区”,那么查询码就要为“CLKQ”这个查询码该怎样提取?貌似有多音字的就比较难搞

怎样快速取查询码?
比如“草类库区”,那么查询码就要为“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 @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)   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 @re=letter+@re,@strlen=@strlen-1      from @t a where chr<=substring(@str,@strlen,1)      order by chr desc    if @@rowcount=0      select @re=substring(@str,@strlen,1)+@re,@strlen=@strlen-1  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   @re=letter+@re,@strlen=@strlen-1             from   @t   a   where   chr <=substring(@str,@strlen,1)             order   by   chr   desc         if   @@rowcount=0             select   @re=substring(@str,@strlen,1)+@re,@strlen=@strlen-1     end     return(@re) end 


[解决办法]
[url=SQL汉字转拼音函数][/url]

热点排行