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

--可将字符串转换为全角或半角的
CREATE  FUNCTION [dbo].[f_Convert]
(
  @str NVARCHAR(4000)=N'' ,   --要转换的字符串   
  @flag BIT=0               --转换标志,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)=N'' )
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
--获取表所有字段
CREATE FUNCTION F_GetTbField
(
  @Tbname NVARCHAR(100) = '' ,
  @Flag BIT = 0   --0，默认取所有字段，1，除去增量字段
)
RETURNS VARCHAR(MAX)
AS 
BEGIN
    DECLARE @FieldName VARCHAR(MAX)
    SELECT @FieldName = ISNULL(@FieldName + ',', '') + NAME
        FROM sys.columns
        WHERE object_id = OBJECT_ID(@Tbname, 'U') AND is_identity = CASE @Flag
                                                              WHEN 0
                                                              THEN is_identity
                                                              ELSE 0
                                                              END  
    --RETURN('insert into '+@Tbname+'('+@FieldName+')')
    RETURN(@FieldName)
END
GO