单独一个表中有一个字段 fax ,
数据是:
fax
--------------------
02889305089
073189360399
02889567066
076589811032
02889910939
076921660059
01021663005
076921680649
02121681731
076921682043
073821683063
076921683408需要将 fax 字段中区号与号码折分出来,存放在另一个表或字段中。
结果是:
telecode fax
------------ --------------
028 89305089
0731 89360399
028 89567066
0765 89811032
028 89910939
0769 21660059
010 21663005
0769 21680649
021 21681731
0769 21682043
0738 21683063
0769 21683408
数据是:
fax
--------------------
02889305089
073189360399
02889567066
076589811032
02889910939
076921660059
01021663005
076921680649
02121681731
076921682043
073821683063
076921683408需要将 fax 字段中区号与号码折分出来,存放在另一个表或字段中。
结果是:
telecode fax
------------ --------------
028 89305089
0731 89360399
028 89567066
0765 89811032
028 89910939
0769 21660059
010 21663005
0769 21680649
021 21681731
0769 21682043
0738 21683063
0769 21683408
area no.
北京 010
广州 020
上海 021
天津 022
重庆 023
...
stuff(b.fax,1,len(a.telecode),'') as fax
from 区号表 a,fax b
where b.fax like a.telecode + '%'
from table
from table
if object_id('tempdb.dbo.#') is not null drop table #
create table #(fax varchar(12))
insert into #
select '02889305089' union all
select '073189360399' union all
select '02889567066' union all
select '076589811032' union all
select '02889910939' union all
select '076921660059' union all
select '01021663005' union all
select '076921680649' union all
select '02121681731' union all
select '076921682043' union all
select '073821683063' union all
select '076921683408'with cte as
(
select fax, (case when fax like '0[1-2][0-9]%' then 3 else 4 end)len from #
)
select left(fax,len) telecode, stuff(fax,1,len,'')fax from cte/*
telecode fax
------------ ------------
028 89305089
0731 89360399
028 89567066
0765 89811032
028 89910939
0769 21660059
010 21663005
0769 21680649
021 21681731
0769 21682043
0738 21683063
0769 21683408
*/
--insert into tb values('02889305089')
insert into tb values('073189360399')
insert into tb values('02889567066')
insert into tb values('076589811032')
insert into tb values('02889910939')
insert into tb values('076921660059')
insert into tb values('01021663005')
insert into tb values('076921680649')
insert into tb values('02121681731')
insert into tb values('076921682043')
insert into tb values('073821683063')
insert into tb values('076921683408')
goselect (case when fax like '01%' or fax like '02%' then left(fax,3) else left(fax,4) end) telecode ,
(case when fax like '01%' or fax like '02%' then substring(fax,3,len(fax)) else substring(fax,4,len(fax)) end) fax
from tbdrop table tb/*
telecode fax
-------- --------------------
0731 189360399
028 889567066
0765 589811032
028 889910939
0769 921660059
010 021663005
0769 921680649
021 121681731
0769 921682043
0738 821683063
0769 921683408(所影响的行数为 11 行)
*/
(爱新觉罗.毓华)
号码是折分开了,怎么在TB表分别创建两个字段呢?
create table tb(fax varchar(20))
--insert into tb values('02889305089')
insert into tb values('073189360399')
insert into tb values('02889567066')
insert into tb values('076589811032')
insert into tb values('02889910939')
insert into tb values('076921660059')
insert into tb values('01021663005')
insert into tb values('076921680649')
insert into tb values('02121681731')
insert into tb values('076921682043')
insert into tb values('073821683063')
insert into tb values('076921683408')
go
alter table tb add c1 varchar(10)
go
alter table tb add c2 varchar(10)
goupdate tb
set c1 = (case when left(fax,2) in ('01','02') then left(fax,3) else left(fax,4) end),
c2 = (case when left(fax,2) in ('01','02') then substring(fax,3,len(fax)) else substring(fax,4,len(fax)) end)select * from tbdrop table tb/*
fax c1 c2
-------------------- ---------- ----------
073189360399 0731 189360399
02889567066 028 889567066
076589811032 0765 589811032
02889910939 028 889910939
076921660059 0769 921660059
01021663005 010 021663005
076921680649 0769 921680649
02121681731 021 121681731
076921682043 0769 921682043
073821683063 0738 821683063
076921683408 0769 921683408(所影响的行数为 11 行)
*/