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