SQL语句如下：

DECLARE @temp varchar(50)

DECLARE @post int

DECLARE @endpost int

DECLARE @flag bit

SET @flag = 1

SET @post = 0

SET @temp='1,2,3,4,'

WHILE(@flag = 1)

BEGIN

SET @endpost = charindex(',',@temp)

IF(@endpost > 0)

BEGIN

INSERT INTO [str] (chr) VALUES (substring(@temp,1,@endpost-1))

SET @temp = right(@temp,len(@temp)-@endpost)

END

ELSE

BEGIN

IF(@temp <> '')

BEGIN

INSERT INTO [str] (chr) VALUES (@temp)

SET @flag = 0

END

ELSE

SET @flag = 0

END

END

 

 

我使用的是下面这个版本

BEGIN  

  declare spot SMALLINT;

  declare TId int;

  WHILE (CampaignId_Array <> '') DO  

  SET spot=instr(CampaignId_Array,',');

  IF spot > 0

  THEN  

  SET TId = Cast(LEFT(CampaignId_Array, spot-1) as SIGNED);

  SET CampaignId_Array = RIGHT(CampaignId_Array, LENGTH(CampaignId_Array)-spot);  

  ELSE  

  SET TId = Cast(CampaignId_Array as SIGNED);

  SET CampaignId_Array = '';  

END IF; 

Insert into campaign_asset (campaign_id, asset_id, advertiser_id,created_by) 

  values (TId, Asset_id, Advertiser_id, Created_by);

  END WHILE;

END

 

实际使用

 

DECLARE spot SMALLINT;  -- 分隔符的位置

DECLARE tempCid VARCHAR(64);  -- 循环插入到表tb_sc_tmrecive需要用到的临时的Cid

-- 这里要对 ClassId_Array 进行循环了，进行,分隔循环insert到表`tb_sc_tmrecive`来

        WHILE (ClassId_Array <> '') DO  

          SET spot=INSTR(ClassId_Array,',');

          IF spot > 0

          THEN  

          SET tempCid = LEFT(ClassId_Array, spot-1) ;

          SET ClassId_Array = RIGHT(ClassId_Array, LENGTH(ClassId_Array)-spot);  

          ELSE  

          SET tempCid = ClassId_Array ;

          SET ClassId_Array = '';  

        END IF; 

        INSERT INTO `tb_sc_tmrecive`(`MId`,`CId`) VALUES (intId,tempCid); -- 循环了每个班级id，插入到表 tb_sc_TMRecive

        END WHILE;

    -- 分隔插入结束