…and decode back from char to int.
This is an implemenation I once made to run on an informix database. It is probably used not very often, but I somehow liked the code, therefore I want to share it to everyone:
CREATE FUNCTION EncodeIntToChar( a INT, len INT )
RETURNING VARCHAR(100);
-- Function encodes a numeric value (int) to an alphanumeric representation
-- with a specific length (len)
DEFINE Result VARCHAR(100);
DEFINE i INT;
LET i = 0;
LET Result = '';
FOR i = (len - 1) TO 0
LET Result = TRIM(Result)||CHR( MOD( a / POW( 36, i ), 36 ) + ( 48 + ( CASE WHEN MOD( a / POW( 36, i ), 36 ) > 9 THEN 7 ELSE 0 END ) ) );
END FOR;
RETURN Result;
END FUNCTION;
CREATE FUNCTION CharDecodeToInt( a VARCHAR(100), len INT )
RETURNING INT;
-- Function decodes an alphanumeric value to its integral representation
-- Specification of length is also required
DEFINE Factor, Result, Power, i INT;
LET a = LPAD( RIGHT( a, len ), len, '0' );
LET Result = 0;
FOR i = len TO 1
LET Power = ABS( i - len );
LET Factor = ( ASCII( SUBSTR( a, i, 1 ) ) - 48 - ( CASE WHEN ( ASCII( SUBSTR( a, i, 1 ) )) > 57 THEN 7 ELSE 0 END ) );
LET Result = Result + ( Factor * POW( 36, Power ) );
END FOR;
RETURN Result;
END FUNCTION;
CREATE FUNCTION IncreaseEncoded( AEncoded VARCHAR(100) )
RETURNING VARCHAR(100);
RETURN EncodeIntToChar( CharDecodeToInt( AEncoded, LENGTH( AEncoded ) ) + 1, LENGTH( AEncoded ) );
END FUNCTION;