SQL小技巧一拼音首字母的模糊查询

创建函数，把中文字段翻译成拼音缩写，例如“你好”翻译成“NH”

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION   [dbo].[fun_GetPy](@str   NVARCHAR(4000)) 
RETURNS   NVARCHAR(4000) 
AS 
BEGIN 
DECLARE   @strlen   INT,@re   NVARCHAR(4000) 
DECLARE   @t   TABLE(chr   NCHAR(1)   COLLATE   Chinese_PRC_CI_AS,letter   NCHAR(1)) 
INSERT   INTO   @t(chr,letter) 
    SELECT   '吖 ', 'A '   UNION   ALL   SELECT   '八 ', 'B '   UNION   ALL 
    SELECT   '嚓 ', 'C '   UNION   ALL   SELECT   '咑 ', 'D '   UNION   ALL 
    SELECT   '妸 ', 'E '   UNION   ALL   SELECT   '发 ', 'F '   UNION   ALL 
    SELECT   '旮 ', 'G '   UNION   ALL   SELECT   '铪 ', 'H '   UNION   ALL 
    SELECT   '丌 ', 'J '   UNION   ALL   SELECT   '咔 ', 'K '   UNION   ALL 
    SELECT   '垃 ', 'L '   UNION   ALL   SELECT   '呒 ', 'M '   UNION   ALL 
    SELECT   '拏 ', 'N '   UNION   ALL   SELECT   '噢 ', 'O '   UNION   ALL 
    SELECT   '妑 ', 'P '   UNION   ALL   SELECT   '七 ', 'Q '   UNION   ALL 
    SELECT   '呥 ', 'R '   UNION   ALL   SELECT   '仨 ', 'S '   UNION   ALL 
    SELECT   '他 ', 'T '   UNION   ALL   SELECT   '屲 ', 'W '   UNION   ALL 
    SELECT   '夕 ', 'X '   UNION   ALL   SELECT   '丫 ', 'Y '   UNION   ALL 
    SELECT   '帀 ', 'Z ' 
    SELECT   @strlen=LEN(@str),@re= ' ' 
    WHILE   @strlen> 0 
    BEGIN 
        SELECT   TOP   1   @re=letter+@re,@strlen=@strlen-1 
            FROM   @t   a   WHERE   chr <=SUBSTRING(@str,@strlen,1) 
            ORDER   BY   chr   DESC 
        IF   @@rowcount=0 
            SELECT   @re=SUBSTRING(@str,@strlen,1)+@re,@strlen=@strlen-1 
    END 
    RETURN(@re) 
END
GO