34,588
社区成员
发帖
与我相关
我的任务
分享
update tb set mem_card_no=stuff(mem_card_no,1,4,'2011')
update TB
set mem_card_no='2011'+right(mem_card_no,len(mem_card_no)-4)
where left(mem_card_no,4)='1009'
update tb set mem_card_no=stuff(mem_card_no,1,4,'2011')
CREATE TABLE TB (
mem_card_no VARCHAR(20)
)
INSERT INTO TB
SELECT '1009000001'
UNION ALL
SELECT '1009000002'
UNION ALL
SELECT '1009000003'
SELECT * FROM tb
update tb set mem_card_no=replace(mem_card_no,'1009','2011')
SELECT * FROM tb
mem_card_no
--------------------
2011000001
2011000002
2011000003
(3 row(s) affected)
declare @T table (mem_card_no varchar(16))
insert into @T
select 1009000001 union all
select 1009000002 union all
select 1009000003 union all
select 1009000004 union all
select 1009000005 union all
select 1009000006 union all
select 1009000007 union all
select 1009000008 union all
select 1009000009 union all
select 1009000010 union all
select 1010000011 union all
select 1010000012 union all
select 1010000013 union all
select 1010000014 union all
select 1010000015
select * from @T
update @T set mem_card_no= case when left(mem_card_no,4)=1009
then '2011'+right(mem_card_no,len(mem_card_no)-4) else mem_card_no end
select * from @T
/*
mem_card_no
----------------
2011000001
2011000002
2011000003
2011000004
2011000005
2011000006
2011000007
2011000008
2011000009
2011000010
1010000011
1010000012
1010000013
1010000014
1010000015
*/
--也可以用where 局部更新
update tb set col = '2011' + right(col,len(col)-4)
where left(col,4) = '1009'
update tb set mem_card_no=stuff(mem_card_no,1,4,'2011')
update tb set mem_card_no=stuff(mem_card_no,1,4,'2011')
update tb
set col = '2011' + right(col,len(col)-4)
where left(col,4) = '1009'
update 表 set 字段=REPLACE(字段,'1009','2011')
update TB set mem_card_no=replace(mem_card_no,'1009','2011')