USE [JewelryRetailClientDB]GO

/****** Object: UserDefinedFunction [dbo].[Func_GetPY] Script Date: 10/19/2011 14:21:59 ******/SET ANSI_NULLS ONGO

SET QUOTED_IDENTIFIER ONGO

-- =============================================

-- Author: xoyozo-- Create date: 2010-4-17

-- Description: 提供中文首字母

-- =============================================

CREATE FUNCTION [dbo].[Func_GetPY](

    @str NVARCHAR(4000))

 RETURNS NVARCHAR(4000)

AS

BEGIN

DECLARE @word NCHAR(1),@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 @PYENDGO