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).


A001 => A002



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

Maybe use a base 36 table???


Such a TABLE is easily enough created:

create table an4dig
( an4d char(4) not null
/* , constraint an4dig_pk primary key ( an4d ) */
insert into an4dig
dig1b36 ( AN1D /* AlphaNumeric1Digits */ ) as
( values
, 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:
******** 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 :-)

This thread ...


Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2020 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].