Need Max Right 4 numeric values in list of alpha numeric

Question

Post reply

Need Max Right 4 numeric values in list of alpha numeric

vmon

SSChasing Mays

Points: 624
More actions
June 22, 2006 at 4:00 pm

#169306

I need to find the right four characters of a part number and check to see if they are numeric. If they are great I add 1 and create a new part. If they are not I need to find out if there is part with four charaters that is numeric and add 1 to it and move on. If there is no numeric right four characters then I start at 0001. Here is what I have and I would like to do this without a cursor. Is there are way to sort at list and find the Max Right four numeric characters easily? I have everything except when I have numeric and alpha.
This works when right four of select are all numeric. But when I have alpha and numeric I need the top numeric and ignore alpha. Any ideas? thanks, vmon
   IF (SELECT ISNUMERIC(MAX(CAST(RIGHT(ItemId, 4) AS varchar(4)))) AS NextNum FROM dbo.tblInItem GROUP BY ProductLine HAVING (ProductLine = @ProductLine)) = 1
    BEGIN
     SET @NewItemId = (SELECT @ItemIdBuild + RIGHT('0000' + CAST(MAX(CAST(RIGHT(ItemId, 4) AS INT) + 1) AS VARCHAR (10)),4) AS NextNum
         FROM      dbo.tblInItem
         GROUP BY ProductLine
         HAVING    (ProductLine = @ProductLine))
    END

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

PW-201837 SSC-Insane Points: 20805 More actions · Answer 1

Select @NewItemIdAsInt =

IsNull(Max( Cast(Right(ItemID, 4) As Int) ), 0) + 1 As NextNum

From dbo.tblInItem

Where ItemID Like '[0-9][0-9][0-9][0-9]%'

What happens when a concurrent transaction adds a new item while you're generating this ?

Why not add an Identity column and just seed its initial value to the current max plus 1 ? Then make the NewItemID a calculated column based of the identity ?

vmon SSChasing Mays Points: 624 More actions · Answer 2

Thanks for the help, I will give this a try. I did not think about testing each character. That is a great idea to seed an identity column however the sequential number (suffix) is within a category. Product line A is -0001 to -9999 and product line B is -0001 to -9999. I would need multiple identity columns, etc.

Thanks again.

Jeff Moden SSC Guru Points: 1004279 More actions · Answer 3

Although I have an extreme dislike for "sequence tables", this would be a good place for one.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

stax68 SSChampion Points: 11711 More actions · Answer 4

If your circumstances permit, you should separate the "product line name", as I will call it:

substring

(prodid,1,len(prodid)-(sign(patindex('%[0-9][0-9][0-9][0-9]',prodid))*4))

from the 4-digit number, and move the product line name, along with any data from the product table that is sure to be the same for all 'versions' of the same product line, into a new table, and add a foreign key to the current products table, pointing to the appropraite "product line" As well as being correct design in general terms (avoiding inconsistencies and saving space), this would mean you don't have to constantly split the string to see what's inside it.

Another specific benefit of doing this here would be that you could add a 'sequence' column - this, like a sequence table, contains intentionally denormalised data used for purposes internal to the running of the database, specifically, generating sequential keys in a given table. As you point out in the case of an identity column, which a sequence table often replaces, your needs are slightly different since you need to keep a separate sequence for each "product line". So a sequence column would fit your needs:

In your new "product line" table (how have you managed without this table?), you can add a highest_product_no column, which defaults to null and is incremented first to 0 then through the positive integer sequence. All processes which want to insert new records into the product table (now a child of "product line") will have to increment this value using a rowlock if possible, before inserting the new record(s). You can set up a function for returning a block of key values (normally only one at a time, I would guess) which is used in updates. If you don't mind having teh occasional gap in your seuqnces, you don't even need to do this in a transaction. You can reserve the keys now, update at your leisure.

this function would allow you to reserve any number of contiguous keys, and retruns the last of the sequence reserved. so if you specify more than one new record, the range of keys to use starts from (return value) - (number of records added) + 1, and ends with (return value).

create function dbo.productline_getlastofnewproductnumberrange(@Pline int, @numberofnewrecords int) returns int
as
update p
set @Pline = isnull(p.highest_product_no,0) + @numberofnewrecords , highest_product_no = @Pline
from productline with(rowlock)
where p.PlineID = @Pline

Once this solution is in place, the full product code can be generated from the product line name concatenated with isnull(replace(str(nullif(product.productseqno,0),4),' ','0'),'')

It might seem a lot of work to make all these changes, but they will pay for themselves every quickly given the problems you will avoid (like this one). I always find the expression 'will pay for it itself very quickly' is one which even the most untechnical manager will listen to, especially if you can think of a recent example when several days were spent fixing a data problem (it doesn't really matter if it's a related problem. You are just illustrating what data problems can be like. You don't need to make too much of that last point though!

Where would managers be if they didn't have people who know what's going on to subtly guide them in the right direction?

PS these remarks may be a bit sketchy - for which apologies, but I've got an interview in a couple of hours' time, so I'm going to prepare for that now. If you're not sure about anything, by all means ask. The code isn't tested.

Tim Wilkinson

"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant

schleep SSChampion Points: 12583 More actions · Answer 5

IF IsNumeric(RIGHT(RTRIM('ABCDEF1234'), 4)) = 1

print 'yay'

else

print 'boo'

As already stated above, get the numbers and the letters into separate columns -- the way it's currently designed is not good, as evidenced by the trouble one has to go through to increment the id by 1. The users only care how it's displayed.

P

Jeff Moden SSC Guru Points: 1004279 More actions · Answer 6

Using ISNUMERIC to determine if something is all numeric digits is a form of "Death by SQL"...

IF IsNumeric(RIGHT(RTRIM('ABCDEF$-1,'), 4)) = 1

print 'yay'

else

print 'boo'

IF IsNumeric(RIGHT(RTRIM('ABCDEF0D01'), 4)) = 1

print 'yay'

else

print 'boo'

IF IsNumeric(RIGHT(RTRIM('ABCDEF23E2'), 4)) = 1

print 'yay'

else

print 'boo'

This is the correct way to detect "is all digits" for the example you gave...

IF RIGHT(RTRIM('ABCDEF$-1,'), 4) NOT LIKE '%[^0-9]%'

print 'yay'

else

print 'boo'

IF RIGHT(RTRIM('ABCDEF0D01'), 4) NOT LIKE '%[^0-9]%'

print 'yay'

else

print 'boo'

IF RIGHT(RTRIM('AABCDEF23E2'), 4) NOT LIKE '%[^0-9]%'

print 'yay'

else

print 'boo'

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

schleep SSChampion Points: 12583 More actions · Answer 7

Perhaps you could explain what you mean about Death by SQL? IsNumeric is a system function with a single precise purpose. In my humble estimation, pattern matching (especially with NOT, LIKE, and "%") is likely gonna be more expensive, and you certainly didn't save yourself any keystrokes...

stax68 SSChampion Points: 11711 More actions · Answer 8

the single precise purpose of isnumeric when used on strings is to determine whether the string conforms to TSQL rules for specifying numeric literals. That is not the same as consisting only of decimal digits:

select

isnumeric('-0e-0'), isnumeric('$.,'), isnumeric('9d9')

Tim Wilkinson

"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant

schleep SSChampion Points: 12583 More actions · Answer 9

schleep

SSChampion

Points: 12583

June 26, 2006 at 9:01 am

#645855

Quite.

Thanks!