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

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

• 10 สิงหาคม 2555 1:54

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?

ตอบทั้งหมด

• 10 สิงหาคม 2555 2:04

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

• 10 สิงหาคม 2555 2:18

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?

• 10 สิงหาคม 2555 2:31

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

• 10 สิงหาคม 2555 2:39

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!

• 10 สิงหาคม 2555 2:40

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!

• 10 สิงหาคม 2555 2:45

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

• 10 สิงหาคม 2555 2:59

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

• 10 สิงหาคม 2555 3:03

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!

• 10 สิงหาคม 2555 3:24

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!