-- 要求查询名字开头以字母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 行受影响)
*/