[轉]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/