如何把任意字符串开头的单号自动累加1
如:
'FS0001'累加1='FS0002'
'FSD0001'累加1='FSD0002'
'FSAD001'累加1='FSAD0002'
'S0001'累加1='S0002'传入一个任意变量,自动累加1,返回
如:
'FS0001'累加1='FS0002'
'FSD0001'累加1='FSD0002'
'FSAD001'累加1='FSAD0002'
'S0001'累加1='S0002'传入一个任意变量,自动累加1,返回
--假设后3位为数字
DECLARE @a VARCHAR(100)
SET @a = 'FS0001'
SELECT SUBSTRING(@a,1,LEN(@a) -3) + RIGHT(1000+ CAST(RIGHT(@a,3) AS INT) + 1,3)
--假设后3位为数字
DECLARE @a VARCHAR(100)
SET @a = 'FSAD001'DECLARE @i INT --字符串长度
DECLARE @p INT --后面多少位是数字
DECLARE @temp VARCHAR(100)SELECT @i = LEN(@a) - 1
SELECT @temp = @a
WHILE @i > 0
BEGIN
IF ISNUMERIC(RIGHT(@a,@i)) = 1
BEGIN
SET @p = @i;
BREAK
END SET @i = @i - 1
ENDSELECT SUBSTRING(@a,1,LEN(@a) - @p) + RIGHT(10000000000+ CAST(RIGHT(@a,@p) AS INT) + 1,@p)
drop table tbcreate table tb
(
tid int primary key identity(1,1),
tName varchar(50)
)
insert into tb(tName)values('FS0001')
insert into tb(tName)values('FSD0001')
insert into tb(tName)values('FSAD001')
insert into tb(tName)values('S0001')
select substring(tName,0,charindex('1',tName,1))+cast(right(tName,1)+1 as varchar)as '编号' from tb
/*
编号
FS0002
FSD0002
FSAD002
S0002
*/
set @str='FS0001'
select substring(@str,1,Patindex('%[0-9]%',@str)-1)
----------------------------------------------------
FS
Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) Jul 9 2008 14:43:34 Copyright (c)
1988-2008 Microsoft Corporation Enterprise Evaluation Edition on Windows NT 5.1 <X86>
(Build 2600: Service Pack 3)
愿和大家共同进步
如有雷同、实属巧合
●●●●●2009-09-07 08:50:34.700●●●●●
★★★★★soft_wsx★★★★★
*/
--编号处理-按日期生成单据编号
--创建得到当前日期的视图,以便在用户定义函中可以获取当前日期
create view dbo.v_getdate
as
select dt=convert(char(6),getdate(),12) --年-月-日 yy-mm-dd
go--得到新编号的编号
alter function dbo.f_nextbh()
returns nvarchar(20)
as
begin
declare @dt char(6)
select @dt=dt from dbo.v_getdate
--return(select 'SBA'+right(10000001+isnull(right(max(bh),6),0),6)
return(select @dt+right(10000001+isnull(right(max(bh),6),0),6) --当前日期+6位流水号(或指定字符加流水号
from tb with(xlock,paglock)
where bh like @dt+'%')
end
go
--在表中应用函数
if OBJECT_ID('tb') is not null drop table tb
create table tb(bh nvarchar(20) primary key default dbo.f_nextbh(),col int)
goinsert tb(col) values(1)
insert tb(col) values(2)
insert tb(col) values(3)
insert tb(col) values(4)
insert tb(col) values(5)
insert tb(col) values(6)
insert tb(bh,col) values(dbo.f_nextbh(),14)
select * from tb/*
bh col
090907000001 1
090907000002 2
090907000003 3
090907000004 4
090907000005 5
090907000006 6
090907000007 14
*/
Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) Jul 9 2008 14:43:34 Copyright (c)
1988-2008 Microsoft Corporation Enterprise Evaluation Edition on Windows NT 5.1 <X86>
(Build 2600: Service Pack 3)
愿和大家共同进步
如有雷同、实属巧合
●●●●●2009-09-07 08:50:34.700●●●●●
★★★★★soft_wsx★★★★★
*/
--编号处理-按日期生成单据编号
--创建得到当前日期的视图,以便在用户定义函中可以获取当前日期
create view dbo.v_getdate
as
select dt=convert(char(6),getdate(),12) --年-月-日 yy-mm-dd
go--得到新编号的编号
alter function dbo.f_nextbh()
returns nvarchar(20)
as
begin
declare @dt char(6)
select @dt=dt from dbo.v_getdate
return(select 'SBA'+right(10000001+isnull(right(max(bh),6),0),6)
--return(select @dt+right(10000001+isnull(right(max(bh),6),0),6) --当前日期+6位流水号(或指定字符加流水号
from tb with(xlock,paglock)
where bh like 'SBA%')
end
go
--在表中应用函数
if OBJECT_ID('tb') is not null drop table tb
create table tb(bh nvarchar(20) default dbo.f_nextbh(),col int)
goinsert tb(col) values(1)
insert tb(col) values(2)
insert tb(col) values(3)
insert tb(col) values(4)
insert tb(col) values(5)
insert tb(col) values(6)
insert tb(bh,col) values(dbo.f_nextbh(),14)
select * from tb/*
bh col
SBA000001 1
SBA000002 2
SBA000003 4
SBA000004 5
SBA000005 6
SBA000006 14
*/
if(object_id('tb','U') is not null)
drop table tb
go
create table tb
(
tid int primary key identity(1,1),
tName varchar(50)
)
insert into tb(tName)values('FS0001')
insert into tb(tName)values('FSD0001')
insert into tb(tName)values('FSAD001')
insert into tb(tName)values('S0001')
insert into tb(tName)values('FSA5001')update tb
set tName=substring(tName,1,Patindex('%[0-9]%',tName)-1)
+right('00000'+ltrim(cast(right(tName,len(tName)-len(substring(tName,1,Patindex('%[0-9]%',tName)-1)))as int)+1),
len(tName)-len(substring(tName,1,Patindex('%[0-9]%',tName)-1))) select * from tb
tid tName
----------- --------------------------------------------------
1 FS0002
2 FSD0002
3 FSAD002
4 S0002
5 FSA5002(5 行受影响)
drop table tbcreate table tb
(
tid int primary key identity(1,1),
tName varchar(50)
)
insert into tb values('FS0001')
insert into tb values('FSD0001')
insert into tb values('FSAD001')
insert into tb values('dd6')select
tb.tName
,substring(tb.tName,0,number) as str
,substring(tb.tName,number,(len(tb.tName)-number+1)) as num
,number as startIdx
,(len(tb.tName)-number+1) as length
from
tb inner join master..spt_values msv
on msv.type = 'p'
and number>0
and len(tb.tName)>=number
and isnumeric(substring(tb.tName,number,(len(tb.tName)-number+1)))=1
and isnumeric(substring(tb.tName,(number-1),1))= 0
drop table tbcreate table tb
(
tid int primary key identity(1,1),
tName varchar(50)
)
insert into tb values('FS0001')
insert into tb values('FS5D9999')
insert into tb values('bb9')
insert into tb values('b4b9uu99')select
tName
,str + right((power(10,length+2) + 1 + num)
,length+sign(len(1 + num)-len(len(0 + num))))-- FSD9999 → FSD10000
,str + right((power(10,length+1) + 1 + num),length)-- FSD9999 → FSD0000
from(
select
tb.tName
,substring(tb.tName,0,number) as str
,substring(tb.tName,number,(len(tb.tName)-number+1)) as num
,(len(tb.tName)-number+1) as length
from
tb inner join master..spt_values msv
on msv.type = 'p'
and number>0
and len(tb.tName)>=number
and isnumeric(substring(tb.tName,number,(len(tb.tName)-number+1)))=1
and isnumeric(substring(tb.tName,(number-1),1))= 0
)as d