34,590
社区成员
发帖
与我相关
我的任务
分享
----------------------------------------------------------------
-- Author :DBA_HuangZJ(发粪涂墙)
-- Date :2014-03-11 12:39:35
-- Version:
-- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)
-- Apr 2 2010 15:48:46
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据[Agent_Call]
if object_id('[Agent_Call]') is not null drop table [Agent_Call]
go
create table [Agent_Call]([Called_NUM] bigint)
insert [Agent_Call]
select 18102261555 union all
select 2018102162222 union all
select 2018912345678 union all
select 2018814567892
--------------生成数据--------------------------
select REPLACE([Called_NUM],'20','')new_Called_NUM
from [Agent_Call]
----------------结果----------------------------
/* new_Called_NUM
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
18102261555
18102162222
18912345678
18814567892
*/
create table Agent_Call (Called_NUM varchar(100))
insert into Agent_Call
select '18102261555' union
select '2018102162222' union
select '2018912345678' union
select '2018814567892'
update
a
set
a.Called_NUM=Temp_linshi.Called_NUM_2
from
Agent_Call a,
(
select
Called_NUM,
substring(Called_NUM,3,len(Called_NUM)) as Called_NUM_2
from
Agent_Call
where
Called_NUM like '20%'
) Temp_linshi
where
a.Called_NUM=Temp_linshi.Called_NUM
结果
Called_NUM
18102261555
18102162222
18814567892
18912345678
update a set a.Called_NUM=Temp_linshi.Called_NUM_2
from Agent_Call a,
(
select
Called_NUM,
substring(Called_NUM,3,len(Called_NUM)) as Called_NUM_2
from
Agent_Call
where
Agent_Call like '20%'
) Temp_linshi
where a.Called_NUM=Temp_linshi.Called_NUM