[转]SQL获取汉字拼音首字母函数

--可支持大字符集20000个汉字!

create function f_ch2py(@chn nchar(1))

returns char(1)

as

begin

declare @n int

declare @c char(1)

set @n = 63

select @n = @n +1,

@c = case chn when @chn then char(@n) else @c end

from(

select top 27 * from (

select chn =

'吖' union all select

'八' union all select

'嚓' union all select

'咑' union all select

'妸' union all select

'发' union all select

'旮' union all select

'铪' union all select

'丌' union all select --because have no 'i'

'丌' union all select

'咔' union all select

'垃' union all select

'嘸' union all select

'拏' union all select

'噢' union all select

'妑' union all select

'七' union all select

'呥' union all select

'仨' union all select

'他' union all select

'屲' union all select --no 'u'

'屲' union all select --no 'v'

'屲' union all select

'夕' union all select

'丫' union all select

'帀' union all select @chn) as a

order by chn COLLATE Chinese_PRC_CI_AS

) as b

return(@c)

end

go

select dbo.f_ch2py('中') --Z

select dbo.f_ch2py('国') --G

select dbo.f_ch2py('人') --R

select dbo.f_ch2py('镆') --M

go

-----------------调用

CREATE FUNCTION F_GetHelpCode (

@cName VARCHAR(20) )

RETURNS VARCHAR(12)

AS

BEGIN

DECLARE @i SMALLINT, @L SMALLINT , @cHelpCode VARCHAR(12), @e VARCHAR(12), @iAscii SMALLINT

SELECT @i=1, @L=0 , @cHelpCode=''

while @L<=12 AND @i<=LEN(@cName) BEGIN

SELECT @e=LOWER(SUBSTRING(@cname,@i,1))

SELECT @iAscii=ASCII(@e)

IF @iAscii>=48 AND @iAscii <=57 OR @iAscii>=97 AND @iAscii <=122 or @iAscii=95

SELECT @cHelpCode=@cHelpCode +@e

ELSE

IF @iAscii>=176 AND @iAscii <=247

SELECT @cHelpCode=@cHelpCode + dbo.f_ch2py(@e)

ELSE SELECT @L=@L-1

SELECT @i=@i+1, @L=@L+1 END

RETURN @cHelpCode

END

GO

--调用

select dbo.F_GetHelpCode('大力')

[@more@]http://h.romance.blog.163.com/blog/static/9612382008625127729/

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/93029/viewspace-1018094/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/93029/viewspace-1018094/