USE [test]

GO

/****** 对象:  UserDefinedFunction [dbo].[GetFirstChar]    脚本日期: 02/22/2019 16:39:06 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

--创建中文截取字符函数

ALTER FUNCTION [dbo].[GetFirstChar](@ChineseString NVARCHAR(4000))

RETURNS NVARCHAR(4000)

AS

BEGIN

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)

ELSE

SET @ReturnString = @ReturnString + @SingleCharacter

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

END

RETURN @ReturnString

END