显示汉字首字母

最新推荐文章于 2022-02-25 22:41:09 发布

转载最新推荐文章于 2022-02-25 22:41:09 发布 · 782 阅读

标签

#string #function #c

数据库专栏收录该内容

10 篇文章

订阅专栏

本文介绍了两种在SQL中实现从输入字符串中提取每个汉字的拼音首字母的方法。第一种方法通过创建一个存储过程，利用循环和条件判断来逐个处理字符串中的字符；第二种方法则通过一系列的查询操作实现相同的功能。

方法一:

drop function dbo.getFirstChar

create function getFirstChar(@str varchar(500))
returns varchar(500)
as
begin
declare
@firstChar varchar(500),
@Chinese_String char(46),
@English_String char(24),
@Chinese_Char varchar(2),
@English_Char char(1),
@i int,
@j int
set @firstChar = ''
set @Chinese_String = '吖八嚓哒讹发旮铪讥咔垃妈拿噢趴七蚺箬他哇夕丫匝'
set @English_String = 'IABCDEFGHJKLMNOPQRSTWXYZ'
set @i = 1
set @j = 1
while @i <= len(@str)
begin
  set @Chinese_Char = substring(@str,@i,1)
  set @j = 1
  while @j <= 24
  begin
    set @English_Char = ''
    if @j = 24
    begin 
      select @English_Char=substring(@English_String,@j,1) where @Chinese_Char >= substring(@Chinese_String,@j-1,1)
    end
    else
    begin 
      select @English_Char=substring(@English_String,@j,1) where @Chinese_Char < substring(@Chinese_String,@j,1)
    end
    if @English_Char <> ''
    begin 
      break
    end
    set @j = @j + 1
  end
  set @firstChar = @firstChar + case when ltrim(rtrim(@English_Char)) = 'I' then @Chinese_Char else ltrim(rtrim(@English_Char)) end 
  set @i = @i + 1
end
return(@firstChar)
end

select dbo.getFirstChar('显示字母')

方法二:

declare @strlen int,@return varchar(500),@ii int   
declare @n int,@c char(1),@chn nchar(1),@str varchar(500)   
set @str = '我爱伟大的周星驰'   
select @strlen=len(@str),@return='',@ii=0   
while @ii<@strlen   
begin   
select @ii=@ii+1,@n=63,@chn=substring(@str,@ii,1)   
if @chn>'z'   
begin   
                 --select @c,char(@n)   
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   
                    -- select @chn,@c   
end   
else set @c='a'   
set @return=@return+@c   
end   
select @return   
--SELECT CHAR(64)