34,576
社区成员
发帖
与我相关
我的任务
分享
---获取中文拼音函数---
create function vIFO_GetPy(@str nvarchar(4000))
returns nvarchar(4000) as
begin
declare @strlen int,@re nvarchar(4000)
declare @t table(chr nchar(1) collate Chinese_PRC_CI_AS,letter nchar(1))
insert into @t(chr,letter)
select '吖', 'A ' union all select '八', 'B ' union all
select '嚓', 'C ' union all select '咑', 'D ' union all
select '妸', 'E ' union all select '发', 'F ' union all
select '旮', 'G ' union all select '铪', 'H ' union all
select '丌', 'J ' union all select '咔', 'K ' union all
select '垃', 'L ' union all select '嘸', 'M ' union all
select '拏', 'N ' union all select '噢', 'O ' union all
select '妑', 'P ' union all select '七', 'Q ' union all
select '呥', 'R ' union all select '仨', 'S ' union all
select '他', 'T ' union all select '屲', 'W ' union all
select '夕', 'X ' union all select '丫', 'Y ' union all
select '帀', 'Z '
select @strlen=len(@str),@re=''
while @strlen>0
begin
select top 1 @re=letter+@re,@strlen=@strlen-1
from @t a where chr <=substring(@str,@strlen,1)
order by chr desc
if @@rowcount=0
select @re=substring(@str,@strlen,1)+@re,@strlen=@strlen-1
end
return(@re)
end
create function [dbo].[chinese_firstletter]
(
@str nvarchar(1)
)
returns nvarchar(1)
as
begin
declare @word nchar(1),@PY nvarchar(4000)
set @PY=''
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)
return @PY
end
go
Select dbo.chinese_firstletter('福田区第五医院')
drop function chinese_firstletter
/*
----
F
(所影响的行数为 1 行)
*/
]--获取拼音首字母函数
go
create function f_GetPy(@str nvarchar(4000))
returns nvarchar(4000)
as
begin
declare @strlen int,@re nvarchar(4000)
declare @t table(chr nchar(1) collate Chinese_PRC_CI_AS,letter nchar(1))
insert into @t(chr,letter)
select '吖','A' union all select '八','B' union all
select '嚓','C' union all select '咑','D' union all
select '妸','E' union all select '发','F' union all
select '旮','G' union all select '铪','H' union all
select '丌','J' union all select '咔','K' union all
select '垃','L' union all select '嘸','M' union all
select '拏','N' union all select '噢','O' union all
select '妑','P' union all select '七','Q' union all
select '呥','R' union all select '仨','S' union all
select '他','T' union all select '屲','W' union all
select '夕','X' union all select '丫','Y' union all
select '帀','Z'
select @strlen=len(@str),@re=''
while @strlen>0
begin
select top 1 @re=letter+@re,@strlen=@strlen-1
from @t a where chr<=substring(@str,@strlen,1)
order by chr desc
if @@rowcount=0
select @re=substring(@str,@strlen,1)+@re,@strlen=@strlen-1
end
return(@re)
end
go
--调用
create table tb(col varchar(20))
insert into tb values('草类库区')
insert into tb values('我恒大无敌')
go
select 按拼音首字母 = dbo.f_GetPy(col) from tb
drop table tb
drop function f_GetPy
/*
按拼音首字母
------------
按拼音首字母
CLKQ
WHDWD
*/