Converting VarChar to Numeric and Increment by 1 then convert back to VarChar

Question

I have a table that is in my customers ERP solution where they are storing Lot Codes for each inventory item being received.  They want to automatically assign and increment the lot code.  I have a stored procedure which works for the first 9 inserts into the lot table but then record 10 won't increment to 11.  The Max function in my stored procedure keeps retrieving number 9 and then adding 1 to get me to 10.  What I need is to retrieve 10 and increment to 11 and so on.

Here is the Stored Procedure I'm using:

   @ReceiptNbr nvarchar(11)

AS

DECLARE @LotNumber as varchar(11)
--Declare @RctNumber as varchar(11)
--SET @RctNumber = '00013685'
SELECT @LotNumber =  MAX(LOTNUMBR) from IV00301 WHERE LEFT(LOTNUMBR,8) = @ReceiptNbr	

IF (SELECT @LotNumber) IS NULL
BEGIN
	SELECT @ReceiptNbr + '-1' 
	
END
ELSE
BEGIN
select 
	LEFT(max(@LotNumber),8) + '-' + Convert(varchar,Convert(integer,substring(max(@LotNumber),10,2))+1)
from IV00301 
where left(@LotNumber,8) = @ReceiptNbr	
END

In my table as I'm inserting my records I am getting this:

LOTNUMBR

00013703-1          
00013703-2          
00013703-3          
00013703-4          
00013703-5          
00013703-6          
00013703-7          
00013703-8          
00013703-9          
00013703-10

So the next returned record from the stored procedure should be 00013703-11 but since 9 is Max in this case it keeps setting it to 00013703-10.

I cannot change the structure of the table and the Lotnumbr field is varchar so somehow in my stored procedure I need to get it to recognize the -10 as 10 and then increase it by 1 to make it -11.  Any ideas?

Answer 1

Try

select substring('00013703-9',1,charindex('-','00013703-9'))+ltrim(cast(cast(substring('00013703-9',charindex('-','00013703-9')+1,len('00013703-9')-charindex('-','00013703-9')) as int)+1 as varchar))
go

and

select substring(@LOTNUMBR,1,charindex('-',@LOTNUMBR))+ltrim(cast(cast(substring(@LOTNUMBR,charindex('-',@LOTNUMBR)+1,len(@LOTNUMBR)-charindex('-',@LOTNUMBR)) as int)+1 as varchar))

....

go

---

Many Thanks & Best Regards, Hua Min

Answer 2

Hua Min,

That sort of works, it is actually returning all the records plust the 1 that I want.

00013703-2
00013703-11
00013703-3
00013703-4
00013703-5
00013703-6
00013703-7
00013703-8
00013703-9
00013703-10

Any idea on how to get it to return just the 00013703-11 record and no others?

Answer 3

I see you are already incrementing it by this

select 
	LEFT(max(@LotNumber),8) + '-' + Convert(varchar,Convert(integer,substring(max(@LotNumber),10,2))+1)
from IV00301 
where left(@LotNumber,8) = @ReceiptNbr	

what do you need?

---

Many Thanks & Best Regards, Hua Min

Answer 4

change your second part to this and try:

Select	Top 1 @ReceiptNbr + '-' + Cast(Max(Cast(Substring(LOTNUMBER,10,2) as int)) Over() + 1 as varchar(2)) As LOTNUMBER
From	IV00301 
WHERE	LEFT(LOTNUMBR,8) = @ReceiptNbr	

---

 | Blog: MSBICOE.com | MCITP - BI, SQL Developer & DBA

Hate to mislead others, if I'm wrong slap me. Thanks!

Answer 5

I see you are already incrementing it by this

select 
	LEFT(max(@LotNumber),8) + '-' + Convert(varchar,Convert(integer,substring(max(@LotNumber),10,2))+1)
from IV00301 
where left(@LotNumber,8) = @ReceiptNbr	

what do you need?

---

Many Thanks & Best Regards, Hua Min

No, this code is not right, it will never go up beyond -10

---

 | Blog: MSBICOE.com | MCITP - BI, SQL Developer & DBA

Hate to mislead others, if I'm wrong slap me. Thanks!

Answer 6

I see you are already incrementing it by this

select 
	LEFT(max(@LotNumber),8) + '-' + Convert(varchar,Convert(integer,substring(max(@LotNumber),10,2))+1)
from IV00301 
where left(@LotNumber,8) = @ReceiptNbr	

what do you need?

---

Many Thanks & Best Regards, Hua Min

No, this code is not right, it will never go up beyond -10

---

 | Blog: MSBICOE.com | MCITP - BI, SQL Developer & DBA

Hate to mislead others, if I'm wrong slap me. Thanks!

Correct! Thanks Steven. Instead of that, he/she can try

select
    LEFT(max(@LotNumber),8) + '-' + Convert(varchar,Convert(integer,substring(max(@LotNumber),10,len(@LotNumber)-10))+1)
from IV00301
where left(@LotNumber,8) = @ReceiptNbr   

---

Many Thanks & Best Regards, Hua Min

Answer 7

You will find life much easier in the long run if you declare columns to be the correct type, and use constraints that force the values to be valid.  So since the right half of your LotNumber must be numeric (and, I presume, between 1 and 99 ?), you will be happiest if you split your lot number into two parts, a left part and a right part.

It's not clear from your description whether the left part is supposed to be numeric, but guessing that it is always a number between 1 and 99999999, I would design my table as something like

Create Table MyTable(
  LotNumber As Right('00000000' + Cast(RecieptNumber As varchar(8)), 8) + '-' + Cast(RecieptNumberSeq As varchar(2)) Persisted,
  RecieptNumber decimal(8) Constraint MyTableRecieptNumberCk Check(RecieptNumber Between 1 And 99999999),
  RecieptNumberSeq decimal(2) Constraint MyTableRecieptNumberSeqCk Check(RecieptNumberSeq Between 1 And 99),
  OtherData varchar(20),
  Constraint MyTablePK Primary Key(LotNumber)
  );

Having done that, it's now very easy to get the last used number for a receipt number and increment it to insert new rows, for example,

Insert MyTable(RecieptNumber, RecieptNumberSeq, OtherData) Select 13703, Coalesce(Max(RecieptNumberSeq) + 1, 1), 'Row A' From MyTable Where RecieptNumber = 13703;
Insert MyTable(RecieptNumber, RecieptNumberSeq, OtherData) Select 13703, Coalesce(Max(RecieptNumberSeq) + 1, 1), 'Row B' From MyTable Where RecieptNumber = 13703;
Insert MyTable(RecieptNumber, RecieptNumberSeq, OtherData) Select 13703, Coalesce(Max(RecieptNumberSeq) + 1, 1), 'Row C' From MyTable Where RecieptNumber = 13703;
Insert MyTable(RecieptNumber, RecieptNumberSeq, OtherData) Select 13703, Coalesce(Max(RecieptNumberSeq) + 1, 1), 'Row D' From MyTable Where RecieptNumber = 13703;
Insert MyTable(RecieptNumber, RecieptNumberSeq, OtherData) Select 13703, Coalesce(Max(RecieptNumberSeq) + 1, 1), 'Row E' From MyTable Where RecieptNumber = 13703;
Insert MyTable(RecieptNumber, RecieptNumberSeq, OtherData) Select 13703, Coalesce(Max(RecieptNumberSeq) + 1, 1), 'Row F' From MyTable Where RecieptNumber = 13703;
Insert MyTable(RecieptNumber, RecieptNumberSeq, OtherData) Select 13703, Coalesce(Max(RecieptNumberSeq) + 1, 1), 'Row G' From MyTable Where RecieptNumber = 13703;
Insert MyTable(RecieptNumber, RecieptNumberSeq, OtherData) Select 13703, Coalesce(Max(RecieptNumberSeq) + 1, 1), 'Row H' From MyTable Where RecieptNumber = 13703;
Insert MyTable(RecieptNumber, RecieptNumberSeq, OtherData) Select 13703, Coalesce(Max(RecieptNumberSeq) + 1, 1), 'Row I' From MyTable Where RecieptNumber = 13703;
Insert MyTable(RecieptNumber, RecieptNumberSeq, OtherData) Select 13703, Coalesce(Max(RecieptNumberSeq) + 1, 1), 'Row J' From MyTable Where RecieptNumber = 13703;
Insert MyTable(RecieptNumber, RecieptNumberSeq, OtherData) Select 13703, Coalesce(Max(RecieptNumberSeq) + 1, 1), 'Row K' From MyTable Where RecieptNumber = 13703;
Insert MyTable(RecieptNumber, RecieptNumberSeq, OtherData) Select 13703, Coalesce(Max(RecieptNumberSeq) + 1, 1), 'Row L' From MyTable Where RecieptNumber = 13703;
-- Check Result
Select LotNumber, OtherData From MyTable Order By RecieptNumber, RecieptNumberSeq;

Tom

Answer 8

Steven,

This actually worked...

select
LEFT(max(@LotNumber),8) + '-' + Convert(varchar,Convert(integer,substring(max(@LotNumber),10,2))+1)
from IV00301 where left(@LotNumber,8) = @ReceiptNbr

I just changed it to return the top 1 record and I got exactly what I needed.

select
top 1 LEFT(max(@LotNumber),8) + '-' + Convert(varchar,Convert(integer,substring(max(@LotNumber),10,2))+1)
from IV00301 where left(@LotNumber,8) = @ReceiptNbr

Greatly Appreciated!

Thanks for your help everyone.

Answer 9

Steven,

This actually worked...

select
LEFT(max(@LotNumber),8) + '-' + Convert(varchar,Convert(integer,substring(max(@LotNumber),10,2))+1)
from IV00301 where left(@LotNumber,8) = @ReceiptNbr

I just changed it to return the top 1 record and I got exactly what I needed.

select
top 1 LEFT(max(@LotNumber),8) + '-' + Convert(varchar,Convert(integer,substring(max(@LotNumber),10,2))+1)
from IV00301 where left(@LotNumber,8) = @ReceiptNbr

Greatly Appreciated!

Thanks for your help everyone.

No, I don't think so. by chance it just worked for your because that -10 by chance comes at top 1, this is neither guaranteed nor correct way.

---

 | Blog: MSBICOE.com | MCITP - BI, SQL Developer & DBA

Hate to mislead others, if I'm wrong slap me. Thanks!