取汉字首个拼音字母

最新推荐文章于 2025-08-15 09:43:18 发布

转载最新推荐文章于 2025-08-15 09:43:18 发布 · 580 阅读

文章标签：

#table #object #insert #function #fun #null

学习日记专栏收录该内容

27 篇文章

订阅专栏

本文介绍了一种使用SQL查询特定范围学生姓名的方法，并通过自定义函数处理中英文混合的情况，确保查询结果能够按照字母顺序正确排序。

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

*/