推荐订阅源

CREATE FUNCTION Fun_GetPY
(
@Str NVARCHAR(4000)
)
RETURNS NVARCHAR(4000)
AS
BEGIN
DECLARE @Word NCHAR(1)
DECLARE @PY NVARCHAR(4000)
SET @PY = ''
WHILE LEN(@Str) > 0
BEGIN
SET @Word = LEFT(@Str, 1)
--如果非汉字字符，返回原字符
SET @PY = @PY + (CASE WHEN UNICODE(@Word) BETWEEN 19968 AND 19968 + 20901
THEN (SELECT TOP 1 PY FROM(
SELECT 'A' AS PY, N'骜' AS WORD
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'咗') T
WHERE WORD >= @Word COLLATE Chinese_PRC_CS_AS_KS_WS
ORDER BY PY ASC)
ELSE @Word
END)
SET @Str = RIGHT(@Str, LEN(@Str) - 1)
END
RETURN @PY
END

-- 使用
update dbo.Dict_Medicine set PyCode = [dbo].[Fun_GetPY](MedicineName)  where ID = 1 ;

• 这个的缺点就是不能够识别多音字。只能做简单的识别，其实还是不太建议使用此方法
• 使用函数而不使用存储过程的原因是函数可以在 Select 语句钟执行。而存储过程不行。存储过程可以返回多个参数，函数只能返回一个。