Get Max Value from VARCHAR substring field

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Get Max Value from VARCHAR substring field

Author

Topic

wldodds
Starting Member

20 Posts

Posted - 2012-08-09 : 18:47:57

I have a stored procedure where I'm trying to get the max last 2 digits if they exist. My stored procedure works fine for getting the max value of the last digits 1-9 however when there is a record in the table such as XXXXXXX-10 it is returning 9 as the max record.

Here is what the stored procedure looks like:

@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

My table that stores the lot numbers has the following values:
00013703-1
00013703-2
00013703-3
00013703-4
00013703-5
00013703-6
00013703-7
00013703-8
00013703-9
00013703-10

What I want the stored procedure to do is return 00013703-11 but instead it keeps returning 00013703-10 becuase it thinks 00013703-9 is the max record.

Any ideas on how to resolve this? My Lot Number field is a varchar adn it cannot be changed so I have to live with it. Ideally if I had 1 show up as 01 and 2 as 02 that would be great as well but I can't seem to get it to work for anything other than the first record when I set the -1 to -01.

Any help is greatly appreciated!

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-09 : 19:40:27

You are seeing this behavior because in string sort, 9 comes before 10 (i.e., it compares one character at a time starting with the left-most. So it picks character 9 which is greater than character 1).

You can convert it to numeric in a variety of ways and compare. What works best for your population of data - I am not sure. For example, one of these would work:

MAX(CAST(REPLACE(LOTNUMBER,'-','') AS INT))
MAX(CAST(STUFF(LOTNUMBER,1,CHARINDEX('-',LOTNUMBER),'') AS INT))

If you have an opportunity to change the table schema, I would keep the receipt part and numeric part after the hyphen in two separate columns. Depending on how normalized you want to make it, you might even consider keeping them in separate tables. If you were to do either of those, you would not run into this problem. Querying, updating and answering various questions about the data becomes much more easier and much more efficient if you normalize it that way.

wldodds
Starting Member

20 Posts

Posted - 2012-08-09 : 20:25:38

Unfortunately I cannot change the table schema as it is hard coded as such from the ERP software manufacturer and seperating the values out into 2 seperate tables isn't an option either.

I tried this:

MAX(CAST(REPLACE(LOTNUMBR,'-','') AS numeric (19,0)))

which returned this: 1370310

Ideally what I would like returned would be: 0001370310

I can do it if I check the length then left fill it with 0 to return the desired last 2 digits '10' in this case. But I'm wondering since the source already has the 000 is there an easier way? Basically the first 8 digits are numerical counters and will continue to increase by 1 each transaction so the solution needs to be somewhat dynamic in that today only 5 of the 8 characters are being used but next year or the year after 6 of the 8 will be used and so on. Am I just stuck becuase there is no way to 0 fill a numeric value?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources