Goodspeed

导航

一个查询拼音首字符的UDF

--//下面的stored procedure 是获得汉字字符串的首字母

if  exists  (select  *  from  dbo.sysobjects  where  id  =
object_id(N'[dbo].[udf_GetFirst_Py]')  and  xtype  in  (N'FN',  N'IF',
N'TF'))
drop  function  [dbo].[udf_GetFirst_Py]
GO
Create  function  udf_GetFirst_Py
(@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
 --//确认输入不为空
        if @strlen=0
 begin
  select @return='Please input chatacter!'
  return(@return)
 end
        --//循环整个字符串,用拼音的首字母替换汉字
           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  '丌'
                                                           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  '屲'
                                                           union  all
select  '屲'
                                                           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=@chn
                       set  @return=@return+@c
           end
           return(@return)
end

go
--//测试
select  dbo.udf_GetFirst_Py('上海市') ,dbo.udf_GetFirst_Py('ab相同test读音')
,dbo.udf_GetFirst_Py('')

posted on 2004-02-21 19:54  Goodspeed  阅读(650)  评论(1编辑  收藏  举报