分割以逗号作为分割符号的字符串并插入到表中

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;
    -- 分隔插入结束