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

if   exists   (select   *   from   dbo.sysobjects   where   id   =   object_id(N'[dbo].[fGetPy]')   and   xtype   in   (N'FN',   N'IF',   N'TF'))  

  drop   function   [dbo].[fGetPy]  

  GO  

   

  --创建取拼音函数  

  create   function   fGetPy(@Str   varchar(500)='')  

  returns   varchar(500)  

  as  

  begin  

  declare   @strlen   int,@return   varchar(500),@ii   int  

  declare   @n   int,@c   char(1),@chn   nchar(1)  

   

  select   @strlen=len(@str),@return='',@ii=0  

  set   @ii=0  

  while   @ii<@strlen  

  begin  

  select   @ii=@ii+1,@n=63,@chn=substring(@str,@ii,1)  

  if   @chn>'z'  

  select   @n   =   @n   +1  

  ,@c   =   case   chn   when   @chn   then   char(@n)   else   @c   end  

  from(  

  select   top   27   *   from   (  

  select   chn   =   '吖'  

  union   all   select   '八'  

  union   all   select   '嚓'  

  union   all   select   '咑'  

  union   all   select   '妸'  

  union   all   select   '发'  

  union   all   select   '旮'  

  union   all   select   '铪'  

  union   all   select   '丌' --because   have   no   'i'  

  union   all   select   '丌'  

  union   all   select   '咔'  

  union   all   select   '垃'  

  union   all   select   '嘸'  

  union   all   select   '拏'  

  union   all   select   '噢'  

  union   all   select   '妑'  

  union   all   select   '七'  

  union   all   select   '呥'  

  union   all   select   '仨'  

  union   all   select   '他'  

  union   all   select   '屲' --no   'u'  

  union   all   select   '屲' --no   'v'  

  union   all   select   '屲'  

  union   all   select   '夕'  

  union   all   select   '丫'  

  union   all   select   '帀'  

  union   all   select   @chn)   as   a  

  order   by   chn   COLLATE   Chinese_PRC_CI_AS    

  )   as   b  

  else   set   @c='a'  

  set   @return=@return+@c  

  end  

  return(@return)  

  end  

   

  go  

  --测试  

  select   dbo.fgetpy('东莞市')   as   东莞市,dbo.fgetpy('ab中c国人')   as   中国人  

   

  --删除拼音函数  

  drop   function   fgetpy   

--1. 按拼音排序

DECLARE @t TABLE(col varchar(2))

INSERT @t SELECT '中'

UNION ALL SELECT '国'

UNION ALL SELECT '人'



SELECT * FROM @t ORDER BY col COLLATE Chinese_PRC_CS_AS_KS_WS

/*--结果

col  

---- 

国

人

中

--*/

GO





/*==========================================*/





--2. 汉字首字母查询处理用户定义函数

CREATE FUNCTION f_GetPY(@str nvarchar(4000))

RETURNS nvarchar(4000)

AS

BEGIN

	DECLARE @py TABLE(

		ch char(1),

		hz1 nchar(1) COLLATE Chinese_PRC_CS_AS_KS_WS,

		hz2 nchar(1) COLLATE Chinese_PRC_CS_AS_KS_WS)

	INSERT @py SELECT 'A',N'吖',N'鏊'

	UNION  ALL SELECT 'B',N'八',N'簿'

	UNION  ALL SELECT 'C',N'嚓',N'错'

	UNION  ALL SELECT 'D',N'哒',N'跺'

	UNION  ALL SELECT 'E',N'屙',N'贰'

	UNION  ALL SELECT 'F',N'发',N'馥'

	UNION  ALL SELECT 'G',N'旮',N'过'

	UNION  ALL SELECT 'H',N'铪',N'蠖'

	UNION  ALL SELECT 'J',N'丌',N'竣'

	UNION  ALL SELECT 'K',N'咔',N'廓'

	UNION  ALL SELECT 'L',N'垃',N'雒'

	UNION  ALL SELECT 'M',N'妈',N'穆'

	UNION  ALL SELECT 'N',N'拿',N'糯'

	UNION  ALL SELECT 'O',N'噢',N'沤'

	UNION  ALL SELECT 'P',N'趴',N'曝'

	UNION  ALL SELECT 'Q',N'七',N'群'

	UNION  ALL SELECT 'R',N'蚺',N'箬'

	UNION  ALL SELECT 'S',N'仨',N'锁'

	UNION  ALL SELECT 'T',N'他',N'箨'

	UNION  ALL SELECT 'W',N'哇',N'鋈'

	UNION  ALL SELECT 'X',N'夕',N'蕈'

	UNION  ALL SELECT 'Y',N'丫',N'蕴'

	UNION  ALL SELECT 'Z',N'匝',N'做'

	DECLARE @i int

	SET @i=PATINDEX('%[吖-做]%' COLLATE Chinese_PRC_CS_AS_KS_WS,@str)

	WHILE @i>0

		SELECT @str=REPLACE(@str,SUBSTRING(@str,@i,1),ch)

			,@i=PATINDEX('%[吖-做]%' COLLATE Chinese_PRC_CS_AS_KS_WS,@str)

		FROM @py

		WHERE SUBSTRING(@str,@i,1) BETWEEN hz1 AND hz2

	RETURN(@str)

END

GO

---测试数据---

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 行）