首页 > 其他 > 详细

拼音首字母查询汉字内容

时间:2015-07-23 13:24:59      阅读:174      评论:0      收藏:0      [点我收藏+]
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           做饭

 

拼音首字母查询汉字内容

原文:http://www.cnblogs.com/Celebrator/p/4670013.html

(0)
(0)
   
举报
评论 一句话评论(0
关于我们 - 联系我们 - 留言反馈 - 联系我们:wmxa8@hotmail.com
© 2014 bubuko.com 版权所有
打开技术之扣,分享程序人生!