批量更新数据表~~在线等~~急!!

hly0024 2010-10-01 05:14:23
现有表如下:
tel1 tel2
02488217288 13504005762
02488217288 13504005762
02488217288 13504005762
02488217288 13504005762
02488217288 13504005762
02425373991 02425373931
02462629838 02462629839

想把所有tel1和tel2两列的 前三位为024的区号去掉 ,这个批量语句怎么写?
谢谢喽~

没分了,不好意思~~
...全文
34 点赞 收藏 4
写回复
4 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
dawugui 2010-10-01
create table tb(tel1 varchar(20), tel2 varchar(20))
insert into tb values('02488217288', '13504005762')
insert into tb values('02488217288', '13504005762')
insert into tb values('02488217288', '13504005762')
insert into tb values('02488217288', '13504005762')
insert into tb values('02488217288', '13504005762')
insert into tb values('02425373991', '02425373931')
insert into tb values('02462629838', '02462629839')
go

update tb
set tel1 = (case when left(tel1,3) = '024' and len(tel1) >= 4 then substring(tel1,4,len(tel1))
when left(tel1,3) = '024' and len(tel1) = 3 then '' else tel1 end),
tel2 = (case when left(tel2,3) = '024' and len(tel2) >= 4 then substring(tel2,4,len(tel2))
when left(tel2,3) = '024' and len(tel2) = 3 then '' else tel2 end)

select * from tb
/*
tel1 tel2
-------------------- --------------------
88217288 13504005762
88217288 13504005762
88217288 13504005762
88217288 13504005762
88217288 13504005762
25373991 25373931
62629838 62629839

(所影响的行数为 7 行)

*/

drop table tb
回复
hly0024 2010-10-01
谢谢啦~~~去试下先.
分数不多,刚使用没几天,呵呵。日后加倍~~
回复
dawugui 2010-10-01
create table tb(tel1 varchar(20), tel2 varchar(20))
insert into tb values('02488217288', '13504005762')
insert into tb values('02488217288', '13504005762')
insert into tb values('02488217288', '13504005762')
insert into tb values('02488217288', '13504005762')
insert into tb values('02488217288', '13504005762')
insert into tb values('02425373991', '02425373931')
insert into tb values('02462629838', '02462629839')
go

update tb
set tel1 = (case when left(tel1,3) = '024' and len(tel1) >= 3 then substring(tel1,4,len(tel1)) else tel1 end),
tel2 = (case when left(tel2,3) = '024' and len(tel2) >= 3 then substring(tel2,4,len(tel2)) else tel2 end)

select * from tb
/*
tel1 tel2
-------------------- --------------------
88217288 13504005762
88217288 13504005762
88217288 13504005762
88217288 13504005762
88217288 13504005762
25373991 25373931
62629838 62629839

(所影响的行数为 7 行)

*/

drop table tb
回复
dawugui 2010-10-01
update tb
set tel1 = (case when left(tel1,3) = '024' and len(tel1) >= 3 then substring(tel1,4,len(tel1)) then tel1 end),
tel2 = (case when left(tel2,3) = '024' and len(tel2) >= 3 then substring(tel2,4,len(tel2)) then tel2 end)
回复
相关推荐
发帖
疑难问题
创建于2007-09-28

2.1w+

社区成员

MS-SQL Server 疑难问题
申请成为版主
帖子事件
创建了帖子
2010-10-01 05:14
社区公告
暂无公告