收集一些非常好用常用的用户函数。分享给大家参考。

--可将字符串转换为全角或半角的
CREATE  FUNCTION [dbo].[f_Convert]
(
  @str NVARCHAR(4000) ,   --要转换的字符串   
  @flag BIT               --转换标志,0转换成半角,1转换成全角   
    
)
RETURNS NVARCHAR(4000)
AS 
BEGIN   
    DECLARE @pat NVARCHAR(8) ,
        @step INT ,
        @i INT ,
        @spc INT   
    IF @flag = 0 
        SELECT  @pat = N'%[！-～]%', @step = -65248,
                @str = REPLACE(@str, N'　', N'   ')   
    ELSE 
        SELECT  @pat = N'%[!-~]%', @step = 65248,
                @str = REPLACE(@str, N'   ', N'　')   
    SET @i = PATINDEX(@pat   COLLATE LATIN1_GENERAL_BIN, @str)   
    WHILE @i > 0 
        SELECT  @str = REPLACE(@str, SUBSTRING(@str, @i, 1),
                               NCHAR(UNICODE(SUBSTRING(@str, @i, 1)) + @step)),
                @i = PATINDEX(@pat   COLLATE LATIN1_GENERAL_BIN, @str)   
    RETURN(@str)   
END   
GO
--获取每个汉字首个拼音
CREATE FUNCTION [dbo].[f_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