"工号自动增加1" 原来的编码规则是什么? 如AAXXXXX AA:前缀 XXXXX:编码 如: --编码最大为5位数字 <如有需要将5替换成需要的数字>CREATE FUNCTION Bes_FillChar (@value int) RETURNS varchar(5) AS BEGIN declare @ret varchar(5) set @ret = cast(@value as varchar(5)) while len(@ret) < 5 set @ret = '0'+@ret Return @ret END2: declare @AA int set @AA = 2 --编码前缀的长度Update TableName set CardNo = left(CartNo,@AA)+dbo.Bes_FillChar(cast(Isnull(Right(CardNo,Len(CardNo)-@AA),'1')+1 as Int))
--先创建上面的自定义函数Bes_FillChar CREATE FUNCTION Bes_FillChar (@value int) RETURNS varchar(5) AS BEGIN declare @ret varchar(5) set @ret = cast(@value as varchar(5)) while len(@ret) < 5 set @ret = '0'+@ret Return @ret END--分步执行以下三条SQL语句 Alter table TableName add Add_ID int identity goUpdate TableName set CardNo='AA'+dbo.Bes_FillChar(Add_ID) goAlter table TableName drop column Add_ID go
create proc aa as declare @id varchar(5) select @rowcount=count(*) from tablename if @rowcount=0 begin select @id=A01 end else begin select @id=rtrim(max(id)) from tablename select @id=right(convert(char(4),convert(int,substring(@id,2,2))+1001),3) insert tablename(id) values(@id) end
create proc aa as declare @id varchar(5) declare @rowcount integer select @rowcount=count(*) from tablename if @rowcount=0 begin select @id='A01' end else begin select @id=rtrim(max(id)) from tablename select @id=right(convert(char(4),convert(int,substring(@id,2,2))+1001),3) insert tablename(id) values(@id) end
如果是MS SQLSERVER 的话只要在建表时把该字段设为主键,并加上IDENTITY属性就可以了.可以查一下SQL SERVER 的帮助.
select 'A'+left('0'+cast(max(substring(字段,patindex('%[0-9]%',字段),len(字段)))+1 as char(2)),2) as a from 表
//如果字段只有"A" select 'A'+left('0'+cast(max(substring(字段,2,字段),len(字段)))+1 as char(2)),2) as a from 表
如: --编码最大为5位数字 <如有需要将5替换成需要的数字>CREATE FUNCTION Bes_FillChar (@value int)
RETURNS varchar(5) AS
BEGIN
declare @ret varchar(5)
set @ret = cast(@value as varchar(5))
while len(@ret) < 5
set @ret = '0'+@ret
Return @ret
END2:
declare @AA int
set @AA = 2 --编码前缀的长度Update TableName set CardNo = left(CartNo,@AA)+dbo.Bes_FillChar(cast(Isnull(Right(CardNo,Len(CardNo)-@AA),'1')+1 as Int))
CREATE FUNCTION Bes_FillChar (@value int)
RETURNS varchar(5) AS
BEGIN
declare @ret varchar(5)
set @ret = cast(@value as varchar(5))
while len(@ret) < 5
set @ret = '0'+@ret
Return @ret
END--分步执行以下三条SQL语句
Alter table TableName add Add_ID int identity
goUpdate TableName set CardNo='AA'+dbo.Bes_FillChar(Add_ID)
goAlter table TableName drop column Add_ID
go
as
declare @id varchar(5)
select @rowcount=count(*) from tablename
if @rowcount=0 begin
select @id=A01
end else begin
select @id=rtrim(max(id)) from tablename
select @id=right(convert(char(4),convert(int,substring(@id,2,2))+1001),3)
insert tablename(id) values(@id)
end
as
declare @id varchar(5)
declare @rowcount integer
select @rowcount=count(*) from tablename
if @rowcount=0 begin
select @id='A01'
end else begin
select @id=rtrim(max(id)) from tablename
select @id=right(convert(char(4),convert(int,substring(@id,2,2))+1001),3)
insert tablename(id) values(@id)
end
from 表
select 'A'+left('0'+cast(max(substring(字段,2,字段),len(字段)))+1 as char(2)),2) as a
from 表