今天遇到一個需求，客戶要求在系統客戶端選擇客戶的時候，可以用拼音首拼去快速過濾選擇，此時我們在客戶表里面加多一個拼音首拼字段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                                       &