with T as (select count(0) As NUM from tab where name like '重庆%') insert into tab (name) values('重庆' + case T.NUM when 0 then '' else cast(T.NUm as varchar(10)) end)
你这个需求的难点在于取字符串后面的数字字符,我在百度找到了这样一段代码,应该可以满足你的要求,你可以试试,时间有限,就没有提前测试代码,有问题,改改也行: declare @string varchar(500)set @string = '宁155ML2123232'declare @len intset @len = len(@string)declare @outstr varchar(500)set @outstr = ''declare @tempstr varchar(1)while (@len>0)begin set @tempstr = substring(@string,@len,1) if (@tempstr >= '0' and @tempstr <= '9') begin set @outstr = @tempstr + @outstr end else begin break end set @len = @len - 1endselect @outstr
declare @string varchar(500) set @string = '宁155ML2123232' declare @len int set @len = len(@string) declare @outstr varchar(500) set @outstr = '' declare @tempstr varchar(1) while (@len>0) begin set @tempstr = substring(@string,@len,1) if (@tempstr >= '0' and @tempstr <= '9') begin set @outstr = @tempstr + @outstr end else begin break end set @len = @len - 1endselect @outstr
先取出数据库中已经存在“重庆n”的最大id(你的id肯定是递增的吧),找出n,然后+1。
有个问题,如果删除了一条“重庆n”的数据,这条sql n 的计算就错了。不能找个数,只能找最大的,避免重复
with T as (select isnull(max(cast(replace(name,'重庆','') as int),0) As maxNo from tab where name like '重庆%') insert into tab (name) values('重庆' + case T.maxNo when 0 then '' else cast((T.maxNo +1)as varchar(10)) end)
ID如果设置为自动增长,那么可以根据ID排序,获取最后一次添加的值。 新建测试表 MyTest 设计ID自动增长,SName varchar(30)编写存储过程 if exists(select * from dbo.sysobjects where name='MytestPro' and type='P') drop procedure MytestPro go create procedure MytestPro @MyTestName varchar(30) as declare @SName varchar(30),@ID int,@NewName varchar(30) set @SName = (select SName from MyTest where SName = @MyTestName) if(@SName is null) begin --如果名称不存在则插入数据 insert into MyTest values(@MyTestName) end else begin --如果名称存在 获取最后一次名称记录 set @NewName = ( select top 1 SName FROM MyTest where SName LIKE '%A%' order by ID desc) --截取名称后面的编号 set @ID = SUBSTRING(@NewName,2,len(@NewName)) print convert(varchar(20),(@ID)) set @NewName = @MyTestName+convert(varchar(20),(@ID+1)) insert into MyTest values(@NewName) end调用存储过程 execute MytestPro @MyTestName='A'
感谢各位的热情回复!! 我的最终解决方法是: string str="select isnull(max(substring([需加编号自增字段],3,1)),0) as MaxID from [table] where [需加编号自增字段] like '"+ddl_area.SelectedItem.Text+"%'"; Dataview dv=DBhelperSQL.Query(str).Tables[0].DefaultView; int num=Convert.ToInt32(dv[0]["MaxID"].toString());目前只能做成这样子!
(select count(0) As NUM from tab where name like '重庆%')
insert into tab (name) values('重庆' + case T.NUM when 0 then '' else cast(T.NUm as varchar(10)) end)
declare @string varchar(500)set @string = '宁155ML2123232'declare @len intset @len = len(@string)declare @outstr varchar(500)set @outstr = ''declare @tempstr varchar(1)while (@len>0)begin set @tempstr = substring(@string,@len,1) if (@tempstr >= '0' and @tempstr <= '9') begin set @outstr = @tempstr + @outstr end else begin break end set @len = @len - 1endselect @outstr
原文链接:http://zhidao.baidu.com/question/60858606.html
set @string = '宁155ML2123232'
declare @len int
set @len = len(@string)
declare @outstr varchar(500)
set @outstr = ''
declare @tempstr varchar(1)
while (@len>0)
begin
set @tempstr = substring(@string,@len,1)
if (@tempstr >= '0' and @tempstr <= '9')
begin
set @outstr = @tempstr + @outstr
end
else
begin
break
end
set @len = @len - 1endselect
@outstr
有个问题,如果删除了一条“重庆n”的数据,这条sql n 的计算就错了。不能找个数,只能找最大的,避免重复
with T as
(select isnull(max(cast(replace(name,'重庆','') as int),0) As maxNo from tab where name like '重庆%')
insert into tab (name) values('重庆' + case T.maxNo when 0 then '' else cast((T.maxNo +1)as varchar(10)) end)
新建测试表 MyTest 设计ID自动增长,SName varchar(30)编写存储过程
if exists(select * from dbo.sysobjects where name='MytestPro' and type='P')
drop procedure MytestPro
go
create procedure MytestPro
@MyTestName varchar(30)
as
declare @SName varchar(30),@ID int,@NewName varchar(30)
set @SName = (select SName from MyTest where SName = @MyTestName)
if(@SName is null)
begin
--如果名称不存在则插入数据
insert into MyTest values(@MyTestName)
end
else
begin
--如果名称存在 获取最后一次名称记录
set @NewName = ( select top 1 SName FROM MyTest where SName LIKE '%A%' order by ID desc)
--截取名称后面的编号
set @ID = SUBSTRING(@NewName,2,len(@NewName))
print convert(varchar(20),(@ID))
set @NewName = @MyTestName+convert(varchar(20),(@ID+1))
insert into MyTest values(@NewName)
end调用存储过程
execute MytestPro @MyTestName='A'
我的最终解决方法是:
string str="select isnull(max(substring([需加编号自增字段],3,1)),0) as MaxID from [table] where [需加编号自增字段] like '"+ddl_area.SelectedItem.Text+"%'";
Dataview dv=DBhelperSQL.Query(str).Tables[0].DefaultView;
int num=Convert.ToInt32(dv[0]["MaxID"].toString());目前只能做成这样子!