按汉字拼音首字母查询.sql

SQLServer中实现拼音首字母的查询方法

/*  2008.8.26 1:创建拼音对照表，插入数据
              2:插入自定义函数，注意在前台调用GetPY函数时，需要写成dbo.GetPY,必须携带所有者。
    卢旭 


*/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[pyk]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[pyk]
GO

CREATE TABLE [dbo].[pyk] (
	[PY] [varchar] (1) COLLATE Chinese_PRC_CI_AS NULL ,
	[HZ1] [int] NULL ,
	[HZ2] [int] NULL ,
	[id] [int] IDENTITY (1, 1) NOT NULL 
) ON [PRIMARY]
GO

insert into pyk (py,HZ1,HZ2) values ("A",-20319,-20284)
insert into pyk (py,HZ1,HZ2) values ("B",-20283,-19776)
insert into pyk (py,HZ1,HZ2) values ("C",-19775,-19219)
insert into pyk (py,HZ1,HZ2) values ("D",-19218,-18711)
insert into pyk (py,HZ1,HZ2) values ("D",-19218,-18711)
insert into pyk (py,HZ1,HZ2) values ("E",-18710,-18527)
insert into pyk (py,HZ1,HZ2) values ("F",-18526,-18240)
insert into pyk (py,HZ1,HZ2) values ("G",-18239,-17923)
insert into pyk (py,HZ1,HZ2) values ("I",-17922,-17418)
insert into pyk (py,HZ1,HZ2) values ("J",-17417,-16475)
insert into pyk (py,HZ1,HZ2) values ("K",-16474,-16213)
insert into pyk (py,HZ1,HZ2) values ("L",-16212,-15641)
insert into pyk (py,HZ1,HZ2) values ("M",-15640,-15166)
insert into pyk (py,HZ1,HZ2) values ("N",-15165,-14923)
insert into pyk (py,HZ1,HZ2) values ("O",-14922,-14915)
insert into pyk (py,HZ1,HZ2) values ("P",-14914,-14631)
insert into pyk (py,HZ1,HZ2) values ("Q",-14630,-14150)
insert into pyk (py,HZ1,HZ2) values ("R",-14149,-14091)
insert into pyk (py,HZ1,HZ2) values ("S",-14090,-13319)
insert into pyk (py,HZ1,HZ2) values ("T",-13318,-12839)
insert into pyk (py,HZ1,HZ2) values ("W",-12838,-12557)
insert into pyk (py,HZ1,HZ2) values ("X",-12556,-11848)
insert into pyk (py,HZ1,HZ2) values ("Y",-11847,-11056)
insert into pyk (py,HZ1,HZ2) values ("Z",-11055,-10247)



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

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

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE FUNCTION dbo.GetChinese (@s varchar(1000)) RETURNS varchar(100)
as
  
BEGIN
  DECLARE @hz_code int
  DECLARE @sList   varchar(200)
  declare @iCount  int
  declare @sThis   varchar(10)
  declare @i      int
  set @i=1
  set @iCount=len(@s)  --一共多少个汉字
  set @sList=''
  while @i<=@iCount 
  begin
    set @sThis=SubString(@s,@i,1)
    set @sList=@sList+dbo.GetPY(@sThis)
    set @i=@i+1
  end
  RETURN (@sList)
END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE  function dbo.GetPY
 ( 
    @str nvarchar(10) 
 ) 
returns nvarchar(10) 
as 
begin 
  declare @word nchar(1),@PY nvarchar(10) 

  set @PY='' 

  while len(@str)>0 
  begin 
    set @word=left(@str,1) 

    --如果非汉字字符，返回原字符 
    set @PY=@PY+(case when unicode(@word) between 19968 and 19968+20901 
               then (  
                            select top 1 PY  
                            from  
                            (  
                             select 'A' as PY,N'驁' as word 
                             union all select 'B',N'簿' 
                             union all select 'C',N'錯' 
                     union all select 'D',N'鵽' 
                     union all select 'E',N'樲' 
                     union all select 'F',N'鰒' 
                     union all select 'G',N'腂' 
                     union all select 'H',N'夻' 
                     union all select 'J',N'攈' 
                     union all select 'K',N'穒' 
                     union all select 'L',N'鱳' 
                     union all select 'M',N'旀' 
                     union all select 'N',N'桛' 
                     union all select 'O',N'漚' 
                     union all select 'P',N'曝' 
                     union all select 'Q',N'囕' 
                     union all select 'R',N'鶸' 
                     union all select 'S',N'蜶' 
                     union all select 'T',N'籜' 
                     union all select 'W',N'鶩' 
                     union all select 'X',N'鑂' 
                     union all select 'Y',N'韻' 
                     union all select 'Z',N'咗' 
                      ) T  
                   where word>=@word collate Chinese_PRC_CS_AS_KS_WS  
                   order by PY ASC 
                          )  
                      else @word  
                 end) 
    set @str=right(@str,len(@str)-1) 
  end 

  return @PY 

end




GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO