例
0797-8888888
0791-4578654
010-54587542
020-54871245批量更新为07978888888
07914578654
01054587542
02054871245----------------------还有个数据拆分0797-8888888、15979797900拆分为07978888888
15979797900谢谢,小弟没多少分,谢谢大家
0797-8888888
0791-4578654
010-54587542
020-54871245批量更新为07978888888
07914578654
01054587542
02054871245----------------------还有个数据拆分0797-8888888、15979797900拆分为07978888888
15979797900谢谢,小弟没多少分,谢谢大家
Select @S = '0797-8888888、15979797900'Select Top 100 ID = Identity(Int, 1, 1) Into #T From Syscolumns A, Syscolumns BSelect
ColName = Replace(Substring(@S, B.ID, CharIndex('、', @S + '、', B.ID) - B.ID), '-', '')
From #T B
Where Substring('、' + @S, B.id, 1) = '、'
Order By ColNameDrop Table #T
但是下面那个拆分的还没搞懂什么意思下面那个拆分的我的意思是比如读出tel字段是 0797-8888888、15979797900要拆分为tel1=07978888888
tel2=15979797900谢谢
2、--建立测试环境
create table bb(name varchar(50))
insert into bb select '0797-8888888、15979797900'
--建立临时表
create table #(name varchar(50))
--用游标
declare @name varchar(50)
declare roy cursor for select name from bbopen roy
fetch next from roy into @namewhile (@@fetch_status=0)
begin
set @name=replace(@name,'-','')
while (charindex('、',@name)>0)
begin
insert into # select left(@name,charindex('、',@name)-1)
set @name=stuff(@name,1,charindex('、',@name),'')
end
insert into # select @name
fetch next from roy into @name
end
close roy
deallocate roy
--查询
select * from #name
--------------------------------------------------
07978888888
15979797900(所影响的行数为 2 行)