//字符串首字母

CREATE FUNCTION GetInitialLetter(@ChineseString NVARCHAR())

RETURNS NVARCHAR()

AS

BEGIN

DECLARE @SingleCharacter NCHAR(),

@ReturnString NVARCHAR()

SET @ReturnString = '' 

WHILE LEN(@ChineseString)>

BEGIN

--依次取单个字符

SET @SingleCharacter = LEFT(@ChineseString,) 

----汉字字符，返回字符对应首字母,非汉字字符,返回原字符

IF(UNICODE(@SingleCharacter) BETWEEN  AND +)

SET @ReturnString = @ReturnString +

(SELECT TOP  PY FROM

(SELECT 'A' AS PY,N'骜' AS ChineseCharacters

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'咗'

)SpellingTable

where ChineseCharacters > = @SingleCharacter COLLATE Chinese_PRC_CS_AS_KS_WS

ORDER by PY ASC)

ELSE

SET @ReturnString = @ReturnString + @SingleCharacter

SET @ChineseString = RIGHT(@ChineseString,LEN(@ChineseString)-) 

END

RETURN @ReturnString 

END

GO

//调用方式

SELECT dbo.GetInitialLetter('中锴华章')

//循环

declare @id varchar()

declare @name varchar()

declare my_cursor cursor

for(select id,name from community)

open my_cursor;

fetch next from my_cursor into @id,@name;

while @@FETCH_STATUS=

    begin

        print @name;

        update community set english_name=(dbo.GetInitialLetter(@name)) where id=@id;

        fetch next from my_cursor into @id,@name;

    end

close my_cursor;

deallocate my_cursor;

go