SQL标量值函数：返回汉字拼音首拼

今天遇到一个需求，客户要求在系统客户端选择客户的时候，可以用拼音首拼去快速过滤选择，此时我们在客户表里面加多一个拼音首拼字段CustPY来记录，字段加好了，我们要把所有客户名称的拼音简拼都更新到CustPY字段中。

为此利用以下函数做了个批量更新

执行语句:UPDATE  客户表 SET  CustPY=dbo.[Fn_GetPinYin](客户名称字段)

这样就可以了。需要注意的是：多音字。例如 SELECT dbo.[Fn_GetPinYin]('重庆') 返回ZQ    谨此记录。

-- ============================================= -- 调用：SELECT dbo.[Fn_GetPinYin]('中国') -- Create date: 2015-01-06 -- Description:    返回汉字拼音首拼 -- ============================================= CREATE  FUNCTION [dbo].[Fn_GetPinYin] (@str NVARCHAR(4000)) RETURNS NVARCHAR(4000) AS     BEGIN         DECLARE @Word NCHAR(1) ,             @PY NVARCHAR(4000)

        SET @PY=''

        SET @str=REPLACE(@str, '（', '(')         SET @str=REPLACE(@str, '）', ')')

        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                                       &