拼音首字母查询汉字内容

时间:2022-10-04 08:02:25
if object_id('[pactinfo]') is not null drop table [pactinfo]
go
create table [pactinfo]([id] int,[pactname] varchar(4))
insert [pactinfo]
select 1,'正常' union all
select 2,'中国' union all
select 3,'做饭' union all
select 4,'加发'
  
---引用前辈们的一个函数---
create function   f_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 
 
 
---查询---
select 
  * 
from 
  [pactinfo]
where
  left(dbo.f_GetPy(pactname),1)='Z'
 
---结果---
id          pactname 
----------- -------- 
1           正常
2           中国
3           做饭