Re: How to find next number for an alphanum field? Any good and simple tricks?

On 10-Dec-2015 13:20 -0600, Stone, Joel wrote:

On 04-Dec-2015 18:41 -0600, CRPence wrote:

On 04-Dec-2015 16:38 -0600, Stone, Joel wrote:

I have a length 4 alphanum field which I need to find the next
value (incremented by one value).

Examples:
A001 => A002
BBBB => BBBC
WXYZ => WXZ0

And my shop would like it done in COBOL, but any algorithm will
suffice.

Maybe use a base 36 table???

<<SNIP>>

Such a TABLE is easily enough created:

<<SNIPped: see:
[http://archive.midrange.com/midrange-l/201512/msg00143.html] >>

<<SNIP>>
I wish I could say that I understand what you are doing here. I
think that you are populating the entire 36 ** 4 table entries, and
then stepping into that table to get the next increment.

Really neat use of SQL

<<SNIP>>

Yes. The effect of comma-delimited table-reference specifications is an implicit CROSS JOIN; i.e. the /cross product/ aka Cartesian Product result of combining the sets. Every _combination_ of the base-36 /digits/ generated in the row-values clause are repeated four times for every possible value in each digit position of places 36**3, 36**2, 36**1, and 36**0. Thus the query in the INSERT statement [by implicit CROSS JOIN] is generating all possible base-36 numbers.

Then, indeed, the SQL query SELECT statements given, showed a couple ways to find _the next higher increment_ from a known value that would be supplied as the operand [likely a host variable or parameter marker] of the greater-than predicate.

Perhaps more straightforward, would be seeing the same done with just the decimal digits, and limited to only two digit positions 10**1 and 10**0 [and perhaps more elucidating, my having properly ordered the use of x# as eXponent\digit-position references: x1->10**1 and x0->10**0]:

with
dig1b10 ( dec1Dig /* One Decimal Digit (0 to 9) */ ) as
( values
('0'),('1'),('2'),('3'),('4'),('5'),('6'),('7'),('8'),('9')
)
, dig2b10 ( dec2Dig /* Two Decimal Digits (00 to 99) */ ) as
( select char( x1.dec1Dig concat x0.dec1Dig , 2 )
from dig1b10 x1 /* left-most digit; the tens aka 10**1 */
, dig1b10 x0 /* right-most digit; the ones aka 10**0 */
)
select dec2Dig from dig2b10
-- the above will produce decimal numbers 00 to 99 but of
-- course, as the char(2) string, per every combination of
-- all decimal digits zero to nine in both digit-positions:
-- zero||zero->nine, one||zero->nine, … nine||zero->nine