復(fù)制代碼 代碼如下:
--函數(shù)
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) >= '發(fā)' 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
--對(duì)于漢字特殊字符,不生成拼音碼
if (ascii(@temp)>127) set @temp = ''
--對(duì)于英文中小括號(hào),不生成拼音碼
if @temp = '(' or @temp = ')' set @temp = ''
select @strRet = @temp + @strRet
set @intLen = @intLen - 1
end
return lower(@strRet)
end
go
--調(diào)用
select dbo.fn_getpy('張三')
--返回:zs
答?。?2:
取漢字拼音首字母的存儲(chǔ)過(guò)程
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'錯(cuò)'
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存儲(chǔ)過(guò)程定義的三種方法
- 解析:php調(diào)用MsSQL存儲(chǔ)過(guò)程使用內(nèi)置RETVAL獲取過(guò)程中的return值
- PHP調(diào)用MsSQL Server 2012存儲(chǔ)過(guò)程獲取多結(jié)果集(包含output參數(shù))的詳解
- mysql存儲(chǔ)過(guò)程 在動(dòng)態(tài)SQL內(nèi)獲取返回值的方法詳解
- SqlServer獲取存儲(chǔ)過(guò)程返回值的實(shí)例
- PostgreSQL中調(diào)用存儲(chǔ)過(guò)程并返回?cái)?shù)據(jù)集實(shí)例
- delphi mysql adbquery數(shù)據(jù)提供程序或其他服務(wù)返回 E_FAIL 狀態(tài)
- sql添加數(shù)據(jù)后返回受影響行數(shù)據(jù)
- Mysql中返回一個(gè)數(shù)據(jù)庫(kù)的所有表名,列名數(shù)據(jù)類(lèi)型備注
- sql獲取存儲(chǔ)過(guò)程返回?cái)?shù)據(jù)過(guò)程解析