现在要生成一个固定长度字符串M(比喻长度为10),这个字符串分作两部分:A+B
A代表不固定长度位字符串,B代表流水号,达不到长度的就用0来填充。
如第一次 A=dfasdf,则M=dfasdf0001
第二次再出现A=dfasdf,则M=dfasdf0002
就是在A不变情况下,B要一直递增。就是说不够10位,就用0来填充。
A代表不固定长度位字符串,B代表流水号,达不到长度的就用0来填充。
如第一次 A=dfasdf,则M=dfasdf0001
第二次再出现A=dfasdf,则M=dfasdf0002
就是在A不变情况下,B要一直递增。就是说不够10位,就用0来填充。
--EX:DECLARE @ID INT
SET @ID=1
--流水号长度为5SELECT 'dfasdf'+RIGHT('00000'+RTRIM(@ID),5)
/*
dfasdf00001*/
select substring('1000001',2,6)根据这个想吧
declare @S varchar(10)
set @A='dfasdf'
declare @i int
set @i=1
while @i<20
begin
set @s=@A+right('0000000'+cast(@i as varchar(5)),5)
print @s
set @i=@i+1
end
/*
dfasd00001
dfasd00002
dfasd00003
dfasd00004
dfasd00005
dfasd00006
dfasd00007
dfasd00008
dfasd00009
dfasd00010
dfasd00011
dfasd00012
dfasd00013
dfasd00014
dfasd00015
dfasd00016
dfasd00017
dfasd00018
dfasd00019*/
SELECT REPLICATE(0,5)00000(1 row(s) affected)--这样去思考吧
--这样。
DECLARE @ID INT
SET @ID=1
SELECT 'dfasdf'+RIGHT('00000'+ltrim(@ID),5)
FROM A
set @A='dfasdf'
select M=@A+right(
isnull((select max(cast(right(M,10-len(@A)) as int)) from tb where left(M,10-len(@A))=@A and isnumeric(right(M,10-len(@A)))=1)+1000000001
,'0000000001')
,10-len(@A))
INSERT #T SELECT 'SDDDWD'
INSERT #T SELECT 'DASD'
INSERT #T SELECT 'DASD'
SELECT * FROM #TSELECT IDENTITY(INT,1,1)NUM,* INTO #A FROM #TSELECT SNO,MNO=SNO+CAST(REPLICATE(0,10-LEN(SNO)-LEN((select count(1)+1 from #A B where B.SNO = A.SNO AND B.NUM<A.NUM ))) AS VARCHAR)
+CAST ((select count(1)+1 from #A B where B.SNO = A.SNO AND B.NUM<A.NUM ) AS VARCHAR)
from #A A
DROP TABLE #TDROP TABLE #A
declare @prefix varchar(10),@M varchar(10)
set @prefix='dfasdf' select @M=@prefix + right(REPLICATE ('0',10-len(@prefix))+convert(varchar,convert(int,right(t.M,10-len(@prefix)))+1),10-len(@prefix)) from(
select max(M) as M from tb where M like @prefix+'[0-9]'
)t
declare @GetNO varchar(50)
declare @currentNO varchar(50)
set @GetNO=''
DECLARE @ID int
declare @zeo varchar(50)
set @zeo=power(10,@lenth)
set @currentNO=@GetNO+RIGHT(substring(@zeo,2,@lenth-len(@ID))+RTRIM(@ID),@lenth)