create table T(BookName varchar(100),BarCode varchar(100),BookInfo varchar(100),BookPrice int)insert into T select '砍死赵微','11008200956','想砍就砍',25
insert into T select '爱死小姿','1100820011957','想爱就爱',65
insert into T select '锤死赵微','11008200958','想锤就锤',25
insert into T select '亲亲小姿','11008200959','想亲就亲',55
insert into T select '抱抱小姿','110082001122960','想抱就抱',45
insert into T select '吻吻小姿','110082001122961','想吻就吻',75--将少2位的更新
update T set BarCode = left(BarCode,8) + '11' + right(BarCode,3) where len(BarCode)=11--将多2位的更新
update T set BarCode = left(BarCode,8) + '11' + right(BarCode,3)where len(BarCode)=15--更新后,将长度不等于13的删除
delete T where len(BarCode)<>13
select * from Tdrop table T
insert into T select '爱死小姿','1100820011957','想爱就爱',65
insert into T select '锤死赵微','11008200958','想锤就锤',25
insert into T select '亲亲小姿','11008200959','想亲就亲',55
insert into T select '抱抱小姿','110082001122960','想抱就抱',45
insert into T select '吻吻小姿','110082001122961','想吻就吻',75--将少2位的更新
update T set BarCode = left(BarCode,8) + '11' + right(BarCode,3) where len(BarCode)=11--将多2位的更新
update T set BarCode = left(BarCode,8) + '11' + right(BarCode,3)where len(BarCode)=15--更新后,将长度不等于13的删除
delete T where len(BarCode)<>13
select * from Tdrop table T
insert into @T select '爱死小姿','1100820011957','想爱就爱',65
insert into @T select '锤死赵微','11008200958','想锤就锤',25
insert into @T select '亲亲小姿','11008200959','想亲就亲',55
insert into @T select '抱抱小姿','110082001122960','想抱就抱',45
insert into @T select '吻吻小姿','110082001122961','想吻就吻',75
update @t set barcode=case when len(barcode)=11 then stuff(barcode,9,0,'11')
when len(barcode)=15 then stuff(barcode,11,2,'')
else barcode
end
select * from @t
create table T(BookName varchar(100),BarCode varchar(100),BookInfo varchar(100),BookPrice int)insert into T select '砍死赵微','11008200956','想砍就砍',25
insert into T select '爱死小姿','1100820011957','想爱就爱',65
insert into T select '锤死赵微','11008200958','想锤就锤',25
insert into T select '亲亲小姿','11008200959','想亲就亲',55
insert into T select '抱抱小姿','110082001122960','想抱就抱',45
insert into T select '吻吻小姿','110082001122961','想吻就吻',75--将少2位的更新
update T set BarCode = left(BarCode,8) + '11' + right(BarCode,3) where len(BarCode)=11--将多2位的更新
update T set BarCode = left(BarCode,8) + '11' + right(BarCode,3)where len(BarCode)=15--更新后,将长度不等于13的删除
delete T where len(BarCode)<>13
select * from T
drop table T
--用substring()、len()等函数组合
create table T(BookName varchar(100),BarCode varchar(100),BookInfo varchar(100),BookPrice int)insert into T select '砍死赵微','11008200956','想砍就砍',25
insert into T select '爱死小姿','1100820011957','想爱就爱',65
insert into T select '锤死赵微','11008200958','想锤就锤',25
insert into T select '亲亲小姿','11008200959','想亲就亲',55
insert into T select '抱抱小姿','110082001122960','想抱就抱',45
insert into T select '吻吻小姿','110082001122961','想吻就吻',75update T set BarCode =
case
when len(BarCode)=11 then substring(BarCode,1,8) + '11' + substring(BarCode,9,3)
when len(BarCode)=15 then substring(BarCode,1,8) + '11' + substring(BarCode,13,3)
else BarCode endselect * from Tdrop table T