获取汉字首字母

 

使用方法

 select dbo.createChineseNameCode('阿')

 

 

 

IF EXISTS(SELECT name FROM sysobjects WHERE name = 'createChineseNameCode' AND type = 'FN')
 DROP function createChineseNameCode
go
create function createChineseNameCode(@inInValue varchar(500))
 returns varchar(500)
as
begin

 declare @str nvarchar(4000)
 declare @word nchar(1),@PY nvarchar(4000)

 set @str = convert(nvarchar(4000), @inInValue)
  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 convert(varchar(500), @PY)

end
go

-- 搜索单个字符形成发音表
declare @chartable table(sound varchar(5), singchar varchar(100))

SET NOCOUNT ON
declare @start int
declare @stop int
set @start =19968
set @stop = @start+20901
while (@start<=@stop)
begin
 DECLARE @char varchar(2)
 SET @char=''
 SELECT @char=dbo.createChineseNameCode(NCHAR(@start))
 set @char = RTRIM(LTRIM(@char))
 if (0 < len(@char))
 begin
  INSERT into @chartable SELECT @char, NCHAR(@start)
 end
 set @start=@start+1
end

-- 根据发音字母进行归类, 按照每行多少个字符的需求生成表
declare @soundtable table(sound varchar(5))
insert into @soundtable select sound from @chartable group by sound order by sound desc

declare @resultTable table(sound varchar(5), charset varchar(500))

declare soundcursor cursor for select * from @soundtable
open soundcursor

declare @sound varchar(5)
declare @charset varchar(400)
fetch next from soundcursor INTO @sound
while @@FETCH_STATUS=0
BEGIN
 
 -- 根据发音读取字符集
 set @charset = ''
 declare charsetcursor cursor for select singchar from @chartable where sound=@sound
 open charsetcursor
 declare @singchar varchar(5)
 fetch next from charsetcursor INTO @singchar
 while @@FETCH_STATUS=0
 BEGIN
  if (100 > len(@charset))-- 每行100个字符
  begin
   set @charset = @charset + @singchar
  end
  else
  begin
   insert into @resultTable(sound, charset) values(@sound, @charset)
   set @charset = @singchar
  end

  fetch next from charsetcursor INTO @singchar
 end 
 -- 循环完毕要检查还有没有数据没有保存
 if (0 < len(@charset))
 begin
  insert into @resultTable(sound, charset) values(@sound, @charset)
  set @charset = ''
 end
 close charsetcursor
 deallocate charsetcursor

 fetch next from soundcursor INTO @sound
END

close soundcursor
deallocate soundcursor

select * from @resultTable order by sound asc