sql-将字符串按指定字符分割插入指定表中

CREATE PROC SPLIT(
@STR VARCHAR(MAX),        --截取字符串
@SPLIT_STR VARCHAR(20),    --截取字符
@TABLE VARCHAR(200)        --存放表名
)
AS
BEGIN
    DECLARE @RETURNS VARCHAR(50)
    
    IF(OBJECT_ID(@TABLE) IS NOT NULL)
    BEGIN 
        SELECT '创建表失败，该表在数据库中已存在。'
        RETURN
    END
    ELSE
    BEGIN
        BEGIN TRY
            /*
            PRINT 'CREATE TABLE ' + @TABLE + '
            (
            ID INT IDENTITY(1,1),
            Character varchar(200)
            )'
            */
            EXEC('CREATE TABLE ' + @TABLE + '
            (
            ID INT IDENTITY(1,1),
            Character varchar(200)
            )')
            
        END TRY
        BEGIN CATCH
            SELECT '创建表出错，错误详情：' + ERROR_MESSAGE()
            RETURN
        END CATCH
    END
    
    DECLARE @CHARINDEX VARCHAR(20) = NULL
    DECLARE @SQL VARCHAR(MAX) = ''
    
    PRINT @CHARINDEX
        PRINT @SQL
    
    WHILE(isnull(@CHARINDEX,1) <> 0)
    BEGIN
        SELECT @CHARINDEX = ISNULL(@CHARINDEX,0)
        
        SELECT @SQL = 'INSERT INTO ' + @TABLE + '(Character)'
            + ' SELECT SUBSTRING(''' + @STR + ''',' + @CHARINDEX + '+1,CASE CHARINDEX(''' + @SPLIT_STR + ''',''' + @STR + ''',' + @CHARINDEX + ' + 1) WHEN 0 THEN LEN(''' + @STR + ''') + 1 ELSE CHARINDEX(''' + @SPLIT_STR + ''',''' + @STR + ''',' + @CHARINDEX + ' + 1) END - ' + @CHARINDEX + ' - 1) 
        WHERE ISNULL(SUBSTRING(''' + @STR + ''',' + @CHARINDEX + '+1,CASE CHARINDEX(''' + @SPLIT_STR + ''',''' + @STR + ''',' + @CHARINDEX + ' + 1) WHEN 0 THEN LEN(''' + @STR + ''') + 1 ELSE CHARINDEX(''' + @SPLIT_STR + ''',''' + @STR + ''',' + @CHARINDEX + ' + 1) END - ' + @CHARINDEX + ' - 1),'''') <> '''''
        
        BEGIN TRY
            EXEC(@SQL)
        END TRY
        BEGIN CATCH
            SELECT '分割错误，错误信息：' + ERROR_MESSAGE()
        END CATCH
        
        --PRINT @CHARINDEX
        --PRINT @SQL
        
        SELECT @CHARINDEX = CHARINDEX(@SPLIT_STR,@STR,ISNULL(@CHARINDEX,0) + 1);
        
        
    END
    EXEC('SELECT * FROM ' + @TABLE)
END
GO

--调用
EXEC SPLIT '2.3.4.3.','.','TEST_1'

--删除表
DROP TABLE TEST_1
--查询表
SELECT * FROM test_1

--删除存储过程
drop proc split

 

转载于:https://www.cnblogs.com/zhoushangwu/p/4097065.html