SQLSERVER Mysql 调试 笔记

//性能

SET STATISTICS IO on;  

SET STATISTICS TIME on;

//Mysql 切分字符串

CREATE  PROCEDURE proc_split(

    inputstring VARCHAR(1000),

    delim CHAR(1)

)

BEGIN

    DECLARE strlen INT;

    DECLARE last_index INT;

    DECLARE cur_index INT;

    DECLARE cur_char VARCHAR(200);

    DECLARE len INT;

    SET cur_index=1;

    SET last_index=0;

    SET strlen=LENGTH(inputstring);  

    DROP TABLE IF EXISTS splittable;

    CREATE TEMPORARY TABLE splittable(

        id INT AUTO_INCREMENT,

        VALUE VARCHAR(20),

        PRIMARY KEY (`ID`),

        UNIQUE KEY `ID` (`ID`)

    ) ;

    WHILE(cur_index<=strlen) DO    

    BEGIN

        IF SUBSTRING(inputstring FROM cur_index FOR 1)=delim OR cur_index=strlen THEN

            SET len=cur_index-last_index-1;

            IF cur_index=strlen THEN

               SET len=len+1;

            END IF;

            INSERT INTO splittable(`value`)VALUES(SUBSTRING(inputstring FROM (last_index+1) FOR len));

            SET last_index=cur_index;

        END IF;

        SET cur_index=cur_index+1;

    END;

    END WHILE;

END;



CALL proc_split('1,2,3,4,3,4,3,4,4',',') ;

SELECT * from  splittable;

//SQL SERVER 切分

CREATE FUNCTION fnSplitStr (
    @sText      NVARCHAR(Max),
    @sDelim     CHAR(1)
)

RETURNS @retArray TABLE (
    value   VARCHAR(100)
)
AS
BEGIN
    DECLARE 
        @posStart        BIGINT,
        @posNext        BIGINT,
        @valLen            BIGINT,
        @sValue            NVARCHAR(100);

    IF @sDelim IS NULL 
    BEGIN
        IF LEN(@sText)>100 SET @sText = SUBSTRING(@sText, 1, 100)
        
        INSERT @retArray (value)
        VALUES (@sText);
    END
    ELSE
    BEGIN
        SET @posStart = 1;

        WHILE @posStart <= LEN(@sText)
        BEGIN
            SET @posNext = CHARINDEX(@sDelim, @sText, @posStart);

            IF @posNext <= 0 
                SET @valLen = LEN(@sText) - @posStart + 1;
            ELSE
                SET @valLen = @posNext - @posStart;

            SET @sValue = SUBSTRING(@sText, @posStart, @valLen);
            SET @posStart = @posStart + @valLen + 1;

            IF LEN(@sValue) > 0
            BEGIN
                IF LEN(@sValue)>100 SET @sValue = SUBSTRING(@sValue, 1, 100)
                
                INSERT @retArray (value)
                VALUES (@sValue);
            END
        END
    END
    RETURN
END


SELECT *  FROM  fnSplitStr('1,2,3,2,,3,3',',')