Return next available product value

Question

Return next available product value

Mike Matthews-166259

SSC Rookie

Points: 42
More actions
April 17, 2007 at 7:19 am

#103342

Folks,
I have a need to return the next available product value from a table. For example the table has available product codes (about 700,000), the column is CHAR(4). A user would enter the characters 12 and we run a query SELECT productCode from Products where productCode like '12%', returns all product codes starting with 12.
How can I return the next available code, if there were no codes that started with 12 and the next available code started with 16 or 20?
Any help would be appreciated.
Thanks,
Mike

Viewing 8 posts - 1 through 8 (of 8 total)

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

I have an idea how to do this, but I need a little more information. What do your product values look like, and how do they increment?

Ken McKelvey SSCoach Points: 18939 More actions · Answer 2

Maybe:

SELECT MIN(ProductCode)

FROM YourTable

WHERE ProductCode >= '12'

Mike Matthews-166259 SSC Rookie Points: 42 More actions · Answer 3

Lynn,

The product code is stored as alpha numeric. The code could be 12AB or AB12. The ideal soluntion would to get the next available column for last character the user entered, in the case that would be '2'. If the next available number set is '6', then return everything that start with '16'.

Ken's reply might suffice (Ken I appreciate the reply), but would like to hear your idea.

Thanks,

Mike

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

Okay, I guess I am getting confused. If you are looking for a code with '12%', how can you get a code like 'AB12'? Still need to know what is the business rule for creating the next product code. If you look for '12%' and don't find any product codes starting with 12, what would the first product code with a 12 look like, 12AA? What it really comes down to is the sequencing of the codes. If it were all numeric, it would start at 1200, then 1201, 1202, etc.

Thanks,

Lynn

Mike Matthews-166259 SSC Rookie Points: 42 More actions · Answer 5

Sorry for the confusion. I was trying to state how the product codes are stored and show the code structure. Let's stick with a code starting with 12, if no codes start with 12 and the next available code starts with 16. How would I write the query?

Thanks,

Mike

SwePeso SSC-Dedicated Points: 39758 More actions · Answer 6

DECLARE

@Sample TABLE (data VARCHAR(20))

INSERT

@Sample

SELECT '1213123213' UNION ALL

SELECT '12sgsgde17' UNION ALL

SELECT '1613123213' UNION ALL

SELECT '12sjk44533' UNION ALL

SELECT '1100892322' UNION ALL

SELECT '12bjetyuew' UNION ALL

SELECT '12dfgadw32'

SELECT

MIN(data)

FROM @Sample

WHERE data > '12'

AND data NOT LIKE '12%'

N 56°04'39.16"
E 12°55'05.25"

Mike Matthews-166259 SSC Rookie Points: 42 More actions · Answer 7

Thanks everyone for the suggestions and help. Ken's suggestion worked just fine.

Thanks again,

Mike