SQL常用自定义函数

时间:2021-11-01 02:07:23

1、字符串转Table(Func_SplitToTable)

CREATE FUNCTION [dbo].[Func_SplitToTable]  
    (  
      @SplitString NVARCHAR(MAX) ,  
      @Separator NVARCHAR(10) = ' '  
    )  
RETURNS @SplitStringsTable TABLE  
    (  
      [id] INT IDENTITY(1, 1) ,  
      [value] NVARCHAR(MAX)  
    )  
AS  
    BEGIN  
        DECLARE @CurrentIndex INT;  
        DECLARE @NextIndex INT;  
        DECLARE @ReturnText NVARCHAR(MAX);  
        SELECT  @CurrentIndex = 1;  
        WHILE ( @CurrentIndex <= LEN(@SplitString) )  
            BEGIN  
                SELECT  @NextIndex = CHARINDEX(@Separator, @SplitString, @CurrentIndex);  
                IF ( @NextIndex = 0  
                     OR @NextIndex IS NULL  
                   )  
                    SELECT  @NextIndex = LEN(@SplitString) + 1;  
                SELECT  @ReturnText = SUBSTRING(@SplitString, @CurrentIndex,  
                                                @NextIndex - @CurrentIndex);  
                INSERT  INTO @SplitStringsTable  
                        ( [value] )  
                VALUES  ( @ReturnText );  
                SELECT  @CurrentIndex = @NextIndex + 1;  
            END;  
        RETURN;  
    END;

二.JSON转Table(Func_ParseJSONToTable,JsonToTable,Func_PivotToNewTable)

一.JSON转Table

ALTER  FUNCTION [dbo].[Func_ParseJSONToTable] ( @JSON NVARCHAR(MAX) )
RETURNS @TX TABLE
    (
      element_id INT IDENTITY(1, 1)
                     NOT NULL ,
      sequenceNo [INT] NULL ,
      parent_ID INT ,
      Object_ID INT ,
      Tx_NAME NVARCHAR(2000) ,
      StringValue NVARCHAR(MAX) NOT NULL ,
      ValueType VARCHAR(10) NOT NULL
    )
AS
    BEGIN  
 
        DECLARE @FirstObject INT ,
            @OpenDelimiter INT ,
            @NextOpenDelimiter INT ,
            @NextCloseDelimiter INT ,
            @Type NVARCHAR(10) ,--
            @NextCloseDelimiterChar CHAR(1) ,
            @Contents NVARCHAR(MAX) ,
            @Start INT ,
            @end INT ,
            @param INT ,
            @EndOfName INT ,
            @token NVARCHAR(200) ,
            @value NVARCHAR(MAX) ,
            @SequenceNo INT ,
            @name NVARCHAR(200) ,
            @parent_ID INT ,
            @lenJSON INT ,
            @characters NCHAR(36) ,
            @result BIGINT ,
            @index SMALLINT ,
            @Escape INT;

DECLARE @Strings TABLE
            (
              String_ID INT IDENTITY(1, 1) ,
              StringValue NVARCHAR(MAX)
            );  
 
        SELECT--initialise the characters to convert hex to ascii  
                @characters = '0123456789abcdefghijklmnopqrstuvwxyz' ,
                @SequenceNo = 0 , --set the sequence no. to something sensible. 
                @parent_ID = 0;  
 
        WHILE 1 = 1 --forever until there is nothing more to do  
            BEGIN  
 
                SELECT  @Start = PATINDEX('%[^a-zA-Z]["]%',
                                          @JSON COLLATE SQL_Latin1_General_CP850_Bin);--next delimited string  
 
                IF @Start = 0
                    BREAK; --no more so drop through the WHILE loop  
 
                IF SUBSTRING(@JSON, @Start + 1, 1) = '"'
                    BEGIN --Delimited Name  
 
                        SET @Start = @Start + 1;  
 
                        SET @end = PATINDEX('%[^\]["]%',
                                            RIGHT(@JSON,
                                                  LEN(@JSON + '|') - @Start));  
 
                    END;  
 
                IF @end = 0 --no end delimiter to last string  
                    BREAK; --no more  
 
                SELECT  @token = SUBSTRING(@JSON, @Start + 1, @end - 1); 
                SELECT  @token = REPLACE(@token, FromString, ToString)
                FROM    ( SELECT    '\"' AS FromString ,
                                    '"' AS ToString
                          UNION ALL
                          SELECT    '\\' ,
                                    '\'
                          UNION ALL
                          SELECT    '\/' ,
                                    '/'
                          UNION ALL
                          SELECT    '\b' ,
                                    CHAR(08)
                          UNION ALL
                          SELECT    '\f' ,
                                    CHAR(12)
                          UNION ALL
                          SELECT    '\n' ,
                                    CHAR(10)
                          UNION ALL
                          SELECT    '\r' ,
                                    CHAR(13)
                          UNION ALL
                          SELECT    '\t' ,
                                    CHAR(09)
                        ) substitutions;  
 
                SELECT  @result = 0 ,
                        @Escape = 1;  
 
 
                WHILE @Escape > 0
                    BEGIN  
 
                        SELECT  @index = 0 ,  
 
                                @Escape = PATINDEX('%\x[0-9a-f][0-9a-f][0-9a-f][0-9a-f]%',
                                                   @token);  
 
                        IF @Escape > 0 --if there is one  
                            BEGIN  
 
                                WHILE @index < 4
                                    BEGIN  
 
                                        SELECT --determine its value  
                                                @result = @result + POWER(16,
                                                              @index)
                                                * ( CHARINDEX(SUBSTRING(@token,
                                                              @Escape + 2 + 3
                                                              - @index, 1),
                                                              @characters) - 1 ) ,
                                                @index = @index + 1;  
 
          
 
                                    END; 
                                SELECT  @token = STUFF(@token, @Escape, 6,
                                                       NCHAR(@result));  
 
                            END;  
 
                    END; 
                INSERT  INTO @Strings
                        ( StringValue )
                        SELECT  @token; 
                SELECT  @JSON = STUFF(@JSON, @Start, @end + 1,
                                      '@string'
                                      + CONVERT(NVARCHAR(5), @@identity));  
 
            END; 
        WHILE 1 = 1  --forever until there is nothing more to do  
            BEGIN 
                SELECT  @parent_ID = @parent_ID + 1; 
                SELECT  @FirstObject = PATINDEX('%[{[[]%',
                                                @JSON COLLATE SQL_Latin1_General_CP850_Bin);--object or array  
 
                IF @FirstObject = 0
                    BREAK;  
 
                IF ( SUBSTRING(@JSON, @FirstObject, 1) = '{' )
                    SELECT  @NextCloseDelimiterChar = '}' ,
                            @Type = 'object';  
 
                ELSE
                    SELECT  @NextCloseDelimiterChar = ']' ,
                            @Type = 'array';  
 
                SELECT  @OpenDelimiter = @FirstObject; 
                WHILE 1 = 1 --find the innermost object or list...  
                    BEGIN  
 
                        SELECT  @lenJSON = LEN(@JSON + '|') - 1; 
                        SELECT  @NextCloseDelimiter = CHARINDEX(@NextCloseDelimiterChar,
                                                              @JSON,
                                                              @OpenDelimiter
                                                              + 1); 
                        SELECT  @NextOpenDelimiter = PATINDEX('%[{[[]%',
                                                              RIGHT(@JSON,
                                                              @lenJSON
                                                              - @OpenDelimiter)COLLATE SQL_Latin1_General_CP850_Bin);--object  
 
                        IF @NextOpenDelimiter = 0
                            BREAK;  
 
                        SELECT  @NextOpenDelimiter = @NextOpenDelimiter
                                + @OpenDelimiter;  
 
                        IF @NextCloseDelimiter < @NextOpenDelimiter
                            BREAK;  
 
                        IF SUBSTRING(@JSON, @NextOpenDelimiter, 1) = '{'
                            SELECT  @NextCloseDelimiterChar = '}' ,
                                    @Type = 'object';  
 
                        ELSE
                            SELECT  @NextCloseDelimiterChar = ']' ,
                                    @Type = 'array';  
 
                        SELECT  @OpenDelimiter = @NextOpenDelimiter;  
 
                    END; 
                SELECT  @Contents = SUBSTRING(@JSON, @OpenDelimiter + 1,
                                              @NextCloseDelimiter
                                              - @OpenDelimiter - 1);  
 
                SELECT  @JSON = STUFF(@JSON, @OpenDelimiter,
                                      @NextCloseDelimiter - @OpenDelimiter + 1,
                                      '@' + @Type
                                      + CONVERT(NVARCHAR(5), @parent_ID));  
 
                WHILE ( PATINDEX('%[A-Za-z0-9@+.e]%',
                                 @Contents COLLATE SQL_Latin1_General_CP850_Bin) ) <> 0
                    BEGIN  
 
                        IF @Type = 'Object'
                            BEGIN  
 
                                SELECT  @SequenceNo = 0 ,
                                        @end = CHARINDEX(':', ' ' + @Contents);
 
                                SELECT  @Start = PATINDEX('%[^A-Za-z@][@]%',
                                                          ' ' + @Contents);--AAAAAAAA  
 
                                SELECT  @token = SUBSTRING(' ' + @Contents,
                                                           @Start + 1,
                                                           @end - @Start - 1) ,
                                        @EndOfName = PATINDEX('%[0-9]%',
                                                              @token COLLATE SQL_Latin1_General_CP850_Bin) ,
                                        @param = RIGHT(@token,
                                                       LEN(@token)
                                                       - @EndOfName + 1);  
 
                                SELECT  @token = LEFT(@token, @EndOfName - 1) ,
                                        @Contents = RIGHT(' ' + @Contents,
                                                          LEN(' ' + @Contents
                                                              + '|') - @end
                                                          - 1);  
 
                                SELECT  @name = StringValue
                                FROM    @Strings
                                WHERE   String_ID = @param; --fetch the name  
 
                            END;  
 
                        ELSE
                            SELECT  @name = NULL ,
                                    @SequenceNo = @SequenceNo + 1;  
 
                        SELECT  @end = CHARINDEX(',', @Contents);
 
                        IF @end = 0
                            SELECT  @end = PATINDEX('%[A-Za-z0-9@+.e][^A-Za-z0-9@+.e]%',
                                                    @Contents + ' ') + 1;  
 
                        SELECT  @Start = PATINDEX('%[^A-Za-z0-9@+.e][A-Za-z0-9@+.e]%',
                                                  ' ' + @Contents);  
 
      --select @start,@end, LEN(@contents+'|'), @contents   
 
                        SELECT  @value = RTRIM(SUBSTRING(@Contents, @Start,
                                                         @end - @Start)) ,
                                @Contents = RIGHT(@Contents + ' ',
                                                  LEN(@Contents + '|') - @end);  
 
                        IF SUBSTRING(@value, 1, 7) = '@object'
                            INSERT  INTO @TX
                                    ( Tx_NAME ,
                                      sequenceNo ,
                                      parent_ID ,
                                      StringValue ,
                                      Object_ID ,
                                      ValueType
                                    )
                                    SELECT  @name ,
                                            @SequenceNo ,
                                            @parent_ID ,
                                            SUBSTRING(@value, 8, 5) ,
                                            SUBSTRING(@value, 8, 5) ,
                                            'object';  
 
                        ELSE
                            IF SUBSTRING(@value, 1, 6) = '@array'
                                INSERT  INTO @TX
                                        ( Tx_NAME ,
                                          sequenceNo ,
                                          parent_ID ,
                                          StringValue ,
                                          Object_ID ,
                                          ValueType
                                        )
                                        SELECT  @name ,
                                                @SequenceNo ,
                                                @parent_ID ,
                                                SUBSTRING(@value, 7, 5) ,
                                                SUBSTRING(@value, 7, 5) ,
                                                'array';  
 
                            ELSE
                                IF SUBSTRING(@value, 1, 7) = '@string'
                                    INSERT  INTO @TX
                                            ( Tx_NAME ,
                                              sequenceNo ,
                                              parent_ID ,
                                              StringValue ,
                                              ValueType
                                            )
                                            SELECT  @name ,
                                                    @SequenceNo ,
                                                    @parent_ID ,
                                                    StringValue ,
                                                    'string'
                                            FROM    @Strings
                                            WHERE   String_ID = SUBSTRING(@value,
                                                              8, 5);  
 
                                ELSE
                                    IF @value IN ( 'true', 'false' )
                                        INSERT  INTO @TX
                                                ( Tx_NAME ,
                                                  sequenceNo ,
                                                  parent_ID ,
                                                  StringValue ,
                                                  ValueType
                                                )
                                                SELECT  @name ,
                                                        @SequenceNo ,
                                                        @parent_ID ,
                                                        @value ,
                                                        'boolean';  
 
                                    ELSE
                                        IF @value = 'null'
                                            INSERT  INTO @TX
                                                    ( Tx_NAME ,
                                                      sequenceNo ,
                                                      parent_ID ,
                                                      StringValue ,
                                                      ValueType
                                                    )
                                                    SELECT  @name ,
                                                            @SequenceNo ,
                                                            @parent_ID ,
                                                            @value ,
                                                            'null';  
 
                                        ELSE
                                            IF PATINDEX('%[^0-9]%',
                                                        @value COLLATE SQL_Latin1_General_CP850_Bin) > 0
                                                INSERT  INTO @TX
                                                        ( Tx_NAME ,
                                                          sequenceNo ,
                                                          parent_ID ,
                                                          StringValue ,
                                                          ValueType
                                                        )
                                                        SELECT
                                                              @name ,
                                                              @SequenceNo ,
                                                              @parent_ID ,
                                                              @value ,
                                                              'real';  
 
                                            ELSE
                                                INSERT  INTO @TX
                                                        ( Tx_NAME ,
                                                          sequenceNo ,
                                                          parent_ID ,
                                                          StringValue ,
                                                          ValueType
                                                        )
                                                        SELECT
                                                              @name ,
                                                              @SequenceNo ,
                                                              @parent_ID ,
                                                              @value ,
                                                              'int';  
 
                        IF @Contents = ' '
                            SELECT  @SequenceNo = 0;  
 
                    END;  
 
            END;  
 
        INSERT  INTO @TX
                ( Tx_NAME ,
                  sequenceNo ,
                  parent_ID ,
                  StringValue ,
                  Object_ID ,
                  ValueType
                )
                SELECT  '-' ,
                        1 ,
                        NULL ,
                        '' ,
                        @parent_ID - 1 ,
                        isnull(@Type,''); 
        RETURN; 
    END;

二.行转列

CREATE FUNCTION [dbo].[Func_PivotToNewTable]
    (
      @Json VARCHAR(MAX) ,--json字符串
      @ColumnName VARCHAR(MAX) --列名
    )
RETURNS VARCHAR(MAX)
AS
    BEGIN
        DECLARE @SQL VARCHAR(MAX);
        SET @SQL = 'DECLARE @table AS JsonToTable ';
        SET @SQL = @SQL + ' INSERT  INTO @table';
        SET @SQL = @SQL + ' SELECT * FROM [Func_ParseJSONToTable](''' + @Json
            + ''')';
        SET @SQL = @SQL + ' SELECT '+@ColumnName+' FROM ( SELECT  Parent_ID ,
                            Tx_NAME ,
                            StringValue
                  FROM      @table
                  WHERE     SequenceNo = 0
                ) p PIVOT( MAX([StringValue]) FOR Tx_NAME IN ( ' + @ColumnName
            + ' ) )AS pvt';  
        RETURN @SQL;
    END;
二.用户自定义类型

CREATE TYPE [dbo].[JsonToTable] AS TABLE(
    [RowID] [int] IDENTITY(1,1) NOT NULL,
    [Element_id] [int] NULL,
    [SequenceNo] [int] NULL,
    [Parent_ID] [int] NULL,
    [Object_ID] [int] NULL,
    [Tx_NAME] [nvarchar](2000) NULL,
    [StringValue] [nvarchar](max) NOT NULL,
    [ValueType] [varchar](10) NOT NULL
)

3、全角半角转化

--===================================  
-- Author:Crazier       
-- Create Date: 2018-3-28    
-- Version: 1.0.0.0       
-- Title: 字符全角半角转换
-- Description: 创建
-- EXEC: SELECT  Func_CommConvertWordAngle_New ( '7',0)
--====================================  
CREATE FUNCTION Func_CommConvertWordAngle_New
    (
      @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