取汉字首个拼音字母

-- 要求查询名字开头以字母A到H的学生的所有名字（包含汉字）并按照26个字母排序.

 

If not object_id('[A]') is null
    Drop table [A]
Go
Create table [A]([ID] int,[Name] nvarchar(15))
Insert A Select
1 ,   'aa' union all select
2  ,  '张三'  union all select
3   , 'oo'  union all select
4   , '李四'  union all select
5    ,'5566'  union all select
6    ,'zz'  union all select
7    ,'ww'  union all select
8    ,'ccc'
go
------------------------------------------------------------
If not object_id('fun_getPY') is null
    Drop function fun_getPY
Go
create function fun_getPY(@str nvarchar(2000))            -----------创建函数
returns nvarchar(4000)
as
begin
 declare @word nchar(1),@PY nvarchar(4000)
 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

select name from [A]
where  left(dbo.fun_getPY(name),1) like '[a-hA-H]'
order by left(dbo.fun_getPY(name),1)

---------------------------------------
name
---------------
aa
ccc

(2 行受影响)

 

----------------------------

If not object_id('[A]') is null
    Drop table [A]
Go
Create table [A]([ID] int,[Name] nvarchar(15))
Insert A Select
1 ,   'aa' union all select
2  ,  '张三'  union all select
3   , 'oo'  union all select
4   , '李四'  union all select
5    ,'5566'  union all select
6    ,'zz'  union all select
7    ,'冰心'  union all select
7    ,'F'  union all select
7    ,'花'  union all select
7    ,'话'  union all select
8    ,'ccc'
------------------------------------------
select * from a where  [Name] like '[a-h吖-夻]%' order by [Name]
/*
ID          Name
----------- ---------------
1           aa
8           ccc
7           F
7           冰心
7           花
7           话

(6 行受影响)

*/