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 ;