22,210
社区成员
发帖
与我相关
我的任务
分享
select * from PCode_Materiel where fbarcode like'00%'
go
update PCode_Materiel set fbarcode='' where fbarcode like '00%'
go
declare @i varchar(20)
declare @y varchar(20)
set @i=0
set @y='0000'
UPDATE PCode_Materiel
set @i=@i+1,fbarcode=@y+@i
where fbarcode = ''
go
update PCode_Materiel set fbarcode= right(fbarcode,5)
where fbarcode like '00%'
--1.查询表里00开头的条形码
select fMaterielNo,fbarcode from PCode_Materiel where fbarcode like '00%'
--2.把00开头的条形码删除为空
update PCode_Materiel set fbarcode='' where fbarcode like '00%'
--3.查询条形码为空的列
select * from PCode_Materiel where fbarcode =''
--4.循环插入条形码
declare @i varchar(20)
set @i='00001'
UPDATE PCode_Materiel
set @i=@i+1,fbarcode=@i
where fbarcode = ''
--5.给插入后的数据添加0
UPDATE PCode_Materiel set fbarcode = right('0000'+ fbarcode,6)
--6.给插入后的数据删除第一位0
update PCode_Materiel set fbarcode=Right([fbarcode],(len([fbarcode])-1)) where fbarcode like '00%'
/*==============================================================================================*/
select right('0000'+ fbarcode,6) from PCode_Materiel
select fMaterielNo,fBarCode from PCode_Materiel
--RIGHT(100000000+字段名,8)
select fMaterielNo,fbarcode from PCode_Materiel where fbarcode like '00%'
select * from PCode_Materiel where fbarcode =''
update PCode_Materiel set fbarcode='' where fbarcode like '00%'
declare @i varchar(20)
set @i='00001'
UPDATE PCode_Materiel
set @i=@i+1,fbarcode=@i
where fbarcode = ''
我是这样写的..能插入..但是结果都不带0 都是1 2 3 4 5 6 这样的...不知道是什么原因update A set 条码=left('00000',5-LEN(b.rn))+convert(nvarchar(5),b.rn) from tableA A
left join (select ROW_NUMBER()over(order by 条码)rn,* from tableA where LEFT(条码,2)='00' and LEN(条码)=8) B on a.条码=b.条码
where LEFT(A.条码,2)='00' and LEN(A.条码)=8
试试这个 你先把你的数据备份一份再测试吧 看看有没有问题