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

我们这里利用了mssql 存储过程获取汉字拼音头字母函数
高级处理方法,有需要的可以参考一下,这里都不需要php,asp.net这类处理哦。
代码如下复制代码

复制代码 代码如下: –函数 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

CREATE function fn_GetPy(@str nvarchar(4000))returns
nvarchar(4000)–WITH ENCRYPTIONasbegindeclare @intLenintdeclare
@strRetnvarchar(4000)declare @temp nvarchar(100)

set @intLen = len(@str)set @strRet = ”

while @intLen 0beginset @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)
= ‘?r’ 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 – 1end

return lower(@strRet)endgo

–调用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’?h’ 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’?a’ 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

You can leave a response, or trackback from your own site.

Leave a Reply

网站地图xml地图