sql存储过程获取汉字拼音头字母函数

代码如下:
--函数
CREATE function fn_GetPy(@str nvarchar(4000))
returns nvarchar(4000)
--WITH ENCRYPTION
as
begin
declare @intLenint
declare @strRetnvarchar(4000)
declare @temp nvarchar(100)
set @intLen = len(@str)
set @strRet = ''
while @intLen > 0
begin
set @temp = ''
select @temp = case
when substring(@str,@intLen,1) >= '帀' then 'Z'
when substring(@str,@intLen,1) >= '丫' then 'Y'
when substring(@str,@intLen,1) >= '夕' then 'X'
when substring(@str,@intLen,1) >= '屲' then 'W'
when substring(@str,@intLen,1) >= '他' then 'T'
when substring(@str,@intLen,1) >= '仨' then 'S'
when substring(@str,@intLen,1) >= '呥' then 'R'
when substring(@str,@intLen,1) >= '七' then 'Q'
when substring(@str,@intLen,1) >= '妑' then 'P'
when substring(@str,@intLen,1) >= '噢' then 'O'
when substring(@str,@intLen,1) >= '拏' then 'N'
when substring(@str,@intLen,1) >= '嘸' then 'M'
when substring(@str,@intLen,1) >= '垃' then 'L'
when substring(@str,@intLen,1) >= '咔' then 'K'
when substring(@str,@intLen,1) >= '丌' then 'J'
when substring(@str,@intLen,1) >= '铪' then 'H'
when substring(@str,@intLen,1) >= '旮' then 'G'
when substring(@str,@intLen,1) >= '发' then 'F'
when substring(@str,@intLen,1) >= '妸' then 'E'
when substring(@str,@intLen,1) >= '咑' then 'D'
when substring(@str,@intLen,1) >= '嚓' then 'C'
when substring(@str,@intLen,1) >= '八' then 'B'
when substring(@str,@intLen,1) >= '吖' then 'A'
else rtrim(ltrim(substring(@str,@intLen,1)))
end
--对于汉字特殊字符，不生成拼音码
if (ascii(@temp)>127) set @temp = ''
--对于英文中小括号，不生成拼音码
if @temp = '(' or @temp = ')' set @temp = ''
select @strRet = @temp + @strRet
set @intLen = @intLen - 1
end
return lower(@strRet)
end
go
--调用
select dbo.fn_getpy('张三')
--返回：zs
答！： 2:
取汉字拼音首字母的存储过程
Create function fun_getPY ( @str nvarchar(4000) )
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
您可能感兴趣的文章:获取SqlServer存储过程定义的三种方法解析:php调用MsSQL存储过程使用内置RETVAL获取过程中的return值PHP调用MsSQL Server 2012存储过程获取多结果集(包含output参数)的详解mysql存储过程 在动态SQL内获取返回值的方法详解SqlServer获取存储过程返回值的实例PostgreSQL中调用存储过程并返回数据集实例delphi mysql adbquery数据提供程序或其他服务返回 E_FAIL 状态sql添加数据后返回受影响行数据Mysql中返回一个数据库的所有表名,列名数据类型备注sql获取存储过程返回数据过程解析

SQL 函数 sql存储过程 字母