现有表如下:
tel1 tel2
02488217288 13504005762
02488217288 13504005762
02488217288 13504005762
02488217288 13504005762
02488217288 13504005762
02425373991 02425373931
02462629838 02462629839想把所有tel1和tel2两列的 前三位为024的区号去掉 ,这个批量语句怎么写?
谢谢喽~没分了,不好意思~~
tel1 tel2
02488217288 13504005762
02488217288 13504005762
02488217288 13504005762
02488217288 13504005762
02488217288 13504005762
02425373991 02425373931
02462629838 02462629839想把所有tel1和tel2两列的 前三位为024的区号去掉 ,这个批量语句怎么写?
谢谢喽~没分了,不好意思~~
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)
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')
goupdate 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
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')
goupdate 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