SQL专场

最新推荐文章于 2024-10-19 14:58:23 发布

黑猴子csd

最新推荐文章于 2024-10-19 14:58:23 发布

阅读量330

点赞数

分类专栏： SQL 文章标签： sql

本文链接：https://blog.csdn.net/u012579604/article/details/47830269

版权

SQL 专栏收录该内容

3 篇文章 0 订阅

订阅专栏


 var sql = ""+
            "SELECT tagname FROM(SELECT * ,ELT(INTERVAL(CONV(HEX(left(CONVERT(tagname USING gbk),1)),16,10)," +
            "0xB0A1,0xB0C5,0xB2C1,0xB4EE,0xB6EA,0xB7A2,0xB8C1,0xB9FE,0xBBF7,0xBFA6,0xC0AC,0xC2E8,0xC4C3,0xC5B6,0xC5BE,0xC6DA,0xC8BB,0xC8F6,0xCBFA,0xCDDA,0xCEF4,0xD1B9,0xD4D1)," +
            "'A','B','C','D','E','F','G','H','J','K','L','M','N','O','P','Q','R','S','T','W','X','Y','Z') as py FROM __TABLE__)as ss WHERE py = UPPER('%s') ";


sql中根据汉字的拼音首字母查询的函数
--引用前辈的--
/****** Object:  UserDefinedFunction [dbo].[f_GetPy]    Script Date: 06/09/2010 10:31:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create function   [dbo].[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



---测试数据---
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,'加发'

---查询---
select 
  * 
from 
  [pactinfo]
where
  dbo.f_GetPy(pactname) like 'Z%'

---结果---
ID          pactname 
----------- -------- 
1           正常
2           中国
3 做饭

（所影响的行数为 3 行）