sql server 切割字符串

DECLARE @str VARCHAR(100) ;
      SET @str = '5,abc,aaa;6,cde,eee;7,gbf,ggg' ;
      DECLARE @s VARCHAR(100) ;
      DECLARE @sql VARCHAR(1000) ;
      SET @sql = '' ;
      DECLARE @sIndex INT ;
      DECLARE @eIndex INT ;
      SET @sIndex = 1 ;
        
      DECLARE @b BIT ;
      SET @b = 0 ;
      
      

        
      WHILE ( 1 = 1 ) 
        BEGIN
            SET @eIndex = CHARINDEX(';', @str, @sIndex) ;
            IF ( @eIndex = 0 ) 
                BEGIN
                    SET @eIndex = LEN(@str) + 1 ;
                    SET @b = 1 ;
                END
            SET @s = SUBSTRING(@str, @sIndex, @eIndex - @sIndex) ;
            SET @sIndex = @eIndex + 1 ;
            --PRINT @s ;
            
            DECLARE @sI INT ;
            DECLARE @eI INT ;
            DECLARE @ss VARCHAR(100) ;
            DECLARE @break BIT ;
            SET @break = 0 ;
            SET @sI = 1 ;
      
            SET @sql = @sql + 'insert into t values('
            WHILE ( 1 = 1 ) 
                BEGIN
                    SET @eI = CHARINDEX(',', @s, @sI) ;
                    IF ( @eI = 0 ) 
                        BEGIN
                            SET @eI = LEN(@s) + 1 ;
                            SET @break = 1 ;
                        END
                    SET @ss = SUBSTRING(@s, @sI, @eI - @sI) ;
                    SET @sql = @sql + @ss + ','
                    SET @sI = @eI + 1 ;
                    --PRINT @ss ;
                    IF ( @break = 1 ) 
                        BREAK ;
                END
            SET @sql = SUBSTRING(@sql, 1, LEN(@sql) - 1) + ');'
            --PRINT @sql ;
             
            IF ( @b = 1 ) 
                BEGIN
                    BREAK
                END
        END
        
      PRINT @sql ; 

转载于:https://www.cnblogs.com/tabin/p/3224590.html