sql中怎么根据汉字的拼音首字母查询

发布网友发布时间：2022-04-21 19:30

共2个回答

懂视网时间：2022-04-29 20:45

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'

sqlserver根据拼音查询数据

标签：sqlserver 数据 substring

热心网友时间：2022-04-29 17:53

-- SQLServer:
---测试数据---
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           做饭

（所影响的行数为 3 行）

全部频道

sql中怎么根据汉字的拼音首字母查询