SqlServer 擷取漢字的拼音首字母

標籤：serve   string   union all   else   lin   sha   bsp   ext   nio   

一、該函數傳入字串，返回資料為：如果為漢字字元，返回該字元的首字母，如果為非漢字字元，則返回本身。
二、用到的知識點：漢字對應的UNICODE值，漢字的定序。
三、資料庫函數：

CREATE FUNCTION GetInitialLetter(@ChineseString NVARCHAR(4000)) RETURNS NVARCHAR(4000) ASBEGIN DECLARE @SingleCharacter NCHAR(1),@ReturnString NVARCHAR(4000) SET @ReturnString = ‘‘ WHILE LEN(@ChineseString)>0 BEGIN--依次取單個字元SET @SingleCharacter = LEFT(@ChineseString,1) ----漢字字元，返回字元對應首字母,非漢字字元,返回原字元IF(UNICODE(@SingleCharacter) BETWEEN 19968 AND 19968+20901)SET @ReturnString = @ReturnString + (SELECT TOP 1 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)ELSESET @ReturnString = @ReturnString + @SingleCharacter SET @ChineseString = RIGHT(@ChineseString,LEN(@ChineseString)-1) END RETURN @ReturnString ENDGO

SELECT dbo.GetInitialLetter(‘加油2018‘)

返回：JY2018

 

SqlServer 擷取漢字的拼音首字母