一.1

A函数代码集:

A.01助记码自动生成函数（中文字符自动生成拼音首字母检索码，方便检索）;

--dbo.f_GetPY(‘测试一下拼音首字母取字’)

CREATE FUNCTION f_GetPY(@str nvarchar(4000))

RETURNS nvarchar(4000)

AS

BEGIN

    DECLARE @py TABLE(

        ch char(1),

        hz1 nchar(1) COLLATE Chinese_PRC_CS_AS_KS_WS,

        hz2 nchar(1) COLLATE Chinese_PRC_CS_AS_KS_WS)

    INSERT @py SELECT 'A',N'吖',N'鏊'

    UNION  ALL SELECT 'B',N'八',N'簿'

    UNION  ALL SELECT 'C',N'嚓',N'错'

    UNION  ALL SELECT 'D',N'哒',N'跺'

    UNION  ALL SELECT 'E',N'屙',N'贰'

    UNION  ALL SELECT 'F',N'发',N'馥'

    UNION  ALL SELECT 'G',N'旮',N'过'

    UNION  ALL SELECT 'H',N'铪',N'蠖'

    UNION  ALL SELECT 'J',N'丌',N'竣'

    UNION  ALL SELECT 'K',N'咔',N'廓'

    UNION  ALL SELECT 'L',N'垃',N'雒'

    UNION  ALL SELECT 'M',N'妈',N'穆'

    UNION  ALL SELECT 'N',N'拿',N'糯'

    UNION  ALL SELECT 'O',N'噢',N'沤'

    UNION  ALL SELECT 'P',N'趴',N'曝'

    UNION  ALL SELECT 'Q',N'七',N'群'

    UNION  ALL SELECT 'R',N'蚺',N'箬'

    UNION  ALL SELECT 'S',N'仨',N'锁'

    UNION  ALL SELECT 'T',N'他',N'箨'

    UNION  ALL SELECT 'W',N'哇',N'鋈'

    UNION  ALL SELECT 'X',N'夕',N'蕈'

    UNION  ALL SELECT 'Y',N'丫',N'蕴'

    UNION  ALL SELECT 'Z',N'匝',N'做'

 

    DECLARE @i int

    SET @i=PATINDEX('%[吖-做]%' COLLATE Chinese_PRC_CS_AS_KS_WS,@str)

    WHILE @i>0

        SELECT @str=REPLACE(@str,SUBSTRING(@str,@i,1),ch)

            ,@i=PATINDEX('%[吖-做]%' COLLATE Chinese_PRC_CS_AS_KS_WS,@str)

        FROM @py

        WHERE SUBSTRING(@str,@i,1) BETWEEN hz1 AND hz2

    RETURN(@str)

END

GO

--测试

select dbo.f_GetPY('邱庆林测试一下拼音首字母取字')

--结果

--‘QQLCSYXPYSZMQZ’