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

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:

-- with SRTSEQ(*LANGIDUNQ) or SRTSEQ(*LANGIDSHR)
create table an4dig
( an4d char(4) not null
/* , constraint an4dig_pk primary key ( an4d ) */
)
;
insert into an4dig
with
dig1b36 ( AN1D /* AlphaNumeric1Digits */ ) as
( values
('0'),('1'),('2'),('3'),('4'),('5'),('6'),('7'),('8'),('9')
,('A'),('B'),('C'),('D'),('E'),('F'),('G'),('H'),('I'),('J')
,('K'),('L'),('M'),('N'),('O'),('P'),('Q'),('R'),('S'),('T')
,('U'),('V'),('W'),('X'),('Y'),('Z')
)
, dig4b36 ( AN4D /* AlphaNumeric4Digits */ ) as
( select char( x0.AN1D concat x1.AN1D concat
x2.AN1D concat x3.AN1D /* x#... etc. */
, 4 /* adj to (#+1) of concatenated x# */ )
from dig1b36 x0 /* low-order digit base^0 */
, dig1b36 x1
, dig1b36 x2
, dig1b36 x3 /* high-order digit base^3 */
)
select an4d
from dig4b36
order by 1
; -- 1679616 rows inserted in AN4DIG in CurSchema
alter table an4dig
add constraint an4dig_pk primary key ( an4d )
;
-- Two examples follow, both using the WXYZ value
-- and both using the same Language Sort Sequence used to
-- create the primary key that could have just as well
-- have been created as a unique keyed INDEX instead.
-- 1) Find next value after 'WXYZ' using FETCH FIRST clause:
select an4d as "4DigitAlphaNumeric"
from an4dig
where an4d > 'WXYZ'
fetch first 1 row only
-- report from above query follows:
"4DigitAlphaNumeric"
WXZ0
******** End of data ********
-- 2) Find next value after 'WXYZ' using MIN aggregate function:
select min(an4d) as "4DigitAlphaNumeric"
from an4dig
where an4d > 'WXYZ'
-- report from above query is same as the prior report shown

I am unsure why, but neither of those SELECT queries implemented as Index-Only, though I presumed both would.

Note: Often such numbering schemes will omit alphabetic characters that might be _difficult to distinguish_ from numbers in some fonts; i.e. often the 'O', 'L', and 'I' [or even 'Q'] might be omitted, such that a base-33 [or base-32] would be the effect. The row-values-clause with multiple values that was used to populate the data in the table is easily enough modified to effect that; obviously my assigned named with 'b36' naming would be debased by that revision :-)