获取拼音首字母函数
SQL code
--获取拼音首字母函数
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 Chine_PRC_CI_AS,letter nchar(1))
inrt into @t(chr,letter)
lect '吖','A' union all lect '八','B' union all
lect '嚓','C' union all lect '咑','D' union all
lect '妸','E' union all 合约价lect '发','F' union all
lect '旮','G' union all lect '铪','H' union all
lect '丌','J' union all lect '咔','K' union all
lect '垃','L' union all lect '嘸','M' union all
lect '拏','N' union all lect '噢','O' union all
lect '妑','P' union all lect '七','Q' union all
lect '呥','R' union all lect '仨','S' union all
lect '他','T' union all lect '屲','W' union all
lect '夕','X' union all lect '丫','Y' union all
lect '帀','Z'
lect @strlen=len(@str),@re=''
while @strlen>0
begin
lect top 1 @re=letter+@re,@strlen=@strlen-1
from @t a where chr<=substring(@str,@strlen,1)
order by chr desc
if @@rowcount=0
lect @re=substring(@str,@strlen,1)+@re,@strlen=@strlen-1
end
return(@re)
end
go
--调用
lect * from a order by ca Name when '其他' then 1 el 0 end,dbo.f_GetPy(Name)
--获取字符串拼音字头
--Pqs 2006-11-20
--@Char 输入的字符串
CREATE function dbo.GetPY (@Char varchar(100))
小学一年级家长会发言稿returns varchar(100) as
begin
--字符串长度 返回数据 遍历字符串位置 遍历的字符
declare @i_Len int, @c_Return varchar(100), @i_pos int,@c varchar(2),@c_CN varchar(100)
tauranga
--初始化数据
--t @str='龙岂池中物'
t @i_Len = len(@Char)
t @c_Return = ''
t @i_pos = 0
--遍历字符串
while (@i_pos < @i_Len )
begin
t @i_pos = @i_pos + 1
t @c_CN = substring(psper@Char, @i_pos, 1)
if (@c_CN > 'z' )
--中文处理，查询匹配
begin
t @c = ca when新概念英语第二册mp3 @c_CN>='帀' then 'Z' when @c_CN>='丫' and @c_CN<'帀' then 'Y' when @c_CN>='夕' and @c_CN<'丫'quoia then 'X' when @c_CN>='屲' and @c_CN<'夕' then 'W'
when @c_CN>='他' and @c_CN<'屲' then 'T' when @c_CN>='仨' and @c_CN<'他' then 'S'伤感的英文名字 when @c_CN>='呥' and @c_CN<'仨' then 'R' when @c_CN>='七' and @c_CN<'呥' then 'Q'
hernwhen @c_CN>='妑' and @c_CN<'七' then 'P' when @c_CN>='噢' and @c_CN<'妑' then 'O' when @c_CN>='拏' and @c_CN<'噢' then 'N' when @c_CN>='嘸' and @c_CN<'拏' then 'M'
when @c_CN>='垃' and @c_CN<'嘸' then 'L' when @c_CN>='咔' and @c_CN<'垃' then 'K' when @c_CN>='丌' and nadia bjorlin@c_CN<'咔' then 'J' when @c_CN>='铪' and @c_CN<'丌' then 'H'
when @c_CN>='旮' and @c_CN<'铪' then 'G' when @c_CN>='发' and @c_CN<'旮' then 'F' when @c_CN>='妸' and @c_CN<'发' then 'E' when @c_CN>='咑' and @c_CN<'妸' then 'D'
when @c_CN>='嚓' and @c_CN<'咑' then 'C' when @c_CN>='八' and @c_CN<'嚓' then 'B' when @c_CN>='吖' and @c_CN<'八' then 'A' El '' End
-- lect top 1 @c = Code from Code_PY where name <= @c_CN
end
el
begin
-
-过滤字符（除了字符、数字和'.'外的所有字符）
if (@c_CN>='a' or (@c_CN>='0' and @c_CN<='9') or @c_CN='.')
t @c=@c_CN
el
t @c=''
end
t @c_Return=@c_Return+isnull(@c ,'')
数字听吧
end
return upper(@c_Return)
end
