ms sql汉字首字母查询

最新推荐文章于 2021-07-15 10:08:22 发布

最新推荐文章于 2021-07-15 10:08:22 发布 · 149 阅读

本文介绍了一个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 * from N_Drugs_Info where  dbo.fgetpy(aaa.dbo.N_Drugs_Info.Drugs_CnName) LIKE '%yww%'