SQL 中将带有分隔符号的字段分割分别插入新表中
好久没有写东西了,今天看到一个园友的那个SQL问题,就写了一个,放在这里做个备份吧
以前也回答了一个这种类似的问题,找了好久也没有看到,特此做个记号
初始表:
Table A
id type
1 xi;hong;shi
2 bei;bei;
3 xihongshibeibei
分割后的表:
Table B
id type groups
1 xi 0
1 hong 1
1 shi 2
2 bei 0
2 bei 1
3 xihongshi 0
以上随便给出一些数据
declare
@CurID int,
@GroupCount int,
@SubName nvarchar(50),--截取出来的字段
@SplitName nvarchar(50) --被分号分割的字段
set @GroupCount=0
begin
DECLARE cur CURSOR FOR
SELECT nameid from testname
OPEN cur
FETCH NEXT FROM cur into @CurID
WHILE @@FETCH_STATUS = 0
begin
select @SplitName =type from A where ID=@CurID
if(CHARINDEX(';',@SplitName )>0)
begin
while(CHARINDEX(';',@SplitName )>0)
begin
SELECT @SubName = LTRIM(RTRIM(SUBSTRING(@SplitName ,1,CHARINDEX(';',@SplitName )-1)))
INSERT INTO B(ID,type,groups)values(@CurID,@SubName,@GroupCount)
SET @SplitName = SUBSTRING(@SplitName ,CHARINDEX(';',@SplitName ) + 1,LEN(@SplitName))
SET @GroupCount = @GroupCount + 1
end
INSERT INTO B(ID,type,groups)
values(@CurID,@SplitName,@GroupCount)
end
else
begin
insert into B select id,type,0 from TestName where ID=@CurID
end
FETCH NEXT FROM cur into @CurID
set @GroupCount=0
end
CLOSE cur
DEALLOCATE cur
end
能够实现,但是要多长时间我没有试验过,就这样,做个备份!