For my project I need to increment and roll over the alphanumeric value, i.e. 0000 -> 0009 -> 000A -> ZZZZ -> 0000
Every solution i found so far is to take a string, convert it from BASE36 to decimal, increment and convert it back to BASE36
First of all, it is too slow because requires a lot of calculations. Secondary, you are limited to the length of string, because if you string, lets say, is 30 bytes long, you will exceed the maximum value of the NUMBER type and you are out of luck.
So, I designed my own function, which is very fast, has no limits on the string length, can increment or decrement a sting by any number, and can hande upper/lower case.
Just want to share it with you. Give it a try!!
-----------------
P.S.
If you do not need to roll a value when it reaches the max just add one extra byte in front of your string when you calling this function.
For example, instead of FN_ALPHANUM_MATH (my_string) do FN_ALPHANUM_MATH ('0'||my_string)
FN_ALPHANUM_MATH('ZZ') = '00', but FN_ALPHANUM_MATH('0'||'ZZ') = '100'
create or replace
FUNCTION FN_ALPHANUM_MATH
(
InputString VARCHAR2
, IncrementBy NUMBER DEFAULT +1
, CompareType CHAR DEFAULT 'T' /* T-text, B-Binary, A-Autodetect, N-Numeric, X-Hexadecimal*/
) RETURN VARCHAR2
IS
/***************************************************************************************************************
Purpose:
This function increments or decrements AND rolls input string of any length
in '0' <--> '9' <--> 'A' <--> 'Z' <--> 'a' <--> 'z' <--> '0' order
--
Input string must be alphanumeric, i.e. must contain only '0' thru '9', 'A' thru 'Z' AND 'a' thru 'z' chars
--
If CompareType is T(ext) then Lower Case chars will be treated as Upper Case, i.e. a = A.
If CompareType is B(inary) then Lower Case chars are not equal to Upper Case, i.e. a > A.
If CompareType is N(umeric) then input must be numeric AND function will roll input in 0 <--> 9 <--> 0 order.
If CompareType is (he)X(adecimal) then function will roll input in 0 - 9 - A - F - 0 order.
If CompareType is A(utodetect) then type of compare will be selected based on input string
--
IncrementBy can be any positive(increment) or negative(decrement) number, default is +1.
Based on IncrementBy function returns next or previous value or NULL IF InputString contains not qualified values
--
Examples for different CompareType:
T: next value of 12Zz is 1300, previous value of a0 is 9Z, next value of Zzz is 000, next value of 1%c is NULL
B: next value of 12Zz is 12a0, previous value of a0 is Zz, next value of Zzz is a00, next value of 1%c is NULL
N: Roll at max for increment ('98',5) = '03', roll at min for decrement ('03', -7) = '96'
X: Roll at max for increment ('FD',5) = '02', roll at min for decrement ('00', -3) = 'FD'
A: next value: 12AZ -> 12B0(T), 12aZ -> 12aa(B), 12f -> 130 (X), 99 -> 00 (D)
--
Copyright garbuya 2010
****************************************************************************************************************/
BAD_INP_TXT_EXCP EXCEPTION;
BAD_COMP_TYP_EXCP EXCEPTION;
c_Err_Msg CONSTANT VARCHAR2(35) := 'FN_ALPHANUM_MATH terminated. ';
c_Upper_Inp CONSTANT VARCHAR2(32760) := UPPER(InputString);
c_Alphabet CONSTANT CHAR(62) := '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz';
c_Compare CONSTANT CHAR(1) := UPPER(CompareType);
v_increment NUMBER := IncrementBy;
v_idx NUMBER := LENGTH(InputString) + 1;
v_mod NUMBER := 0;
v_weight NUMBER := 0;
v_roll NUMBER := 0;
v_low_case NUMBER := 0;
v_base NUMBER := 0;
v_new_val VARCHAR2(32760) := '';
--
BEGIN
--
IF InputString IS NULL
OR IncrementBy IS NULL
OR IncrementBy = 0
OR CompareType IS NULL
THEN
RETURN(InputString);
END IF;
--
v_base := CASE
WHEN c_Compare NOT IN ('A', 'B', 'D', 'T', 'X') THEN 0
WHEN c_Compare = 'D' AND TRANSLATE(InputString, CHR(0)||SUBSTR(c_Alphabet, 1,10), CHR(0)) IS NULL THEN 10
WHEN c_Compare = 'X' AND TRANSLATE(c_Upper_Inp, CHR(0)||SUBSTR(c_Alphabet, 1,16), CHR(0)) IS NULL THEN 16
WHEN c_Compare = 'T' AND TRANSLATE(c_Upper_Inp, CHR(0)||SUBSTR(c_Alphabet, 1,36), CHR(0)) IS NULL THEN 36
WHEN c_Compare = 'B' AND TRANSLATE(InputString, CHR(0)||c_Alphabet , CHR(0)) IS NULL THEN 62
WHEN c_Compare = 'A' AND TRANSLATE(InputString, CHR(0)||SUBSTR(c_Alphabet, 1,10), CHR(0)) IS NULL THEN 10
WHEN c_Compare = 'A' AND TRANSLATE(c_Upper_Inp, CHR(0)||SUBSTR(c_Alphabet, 1,16), CHR(0)) IS NULL THEN 16
WHEN c_Compare = 'A' AND TRANSLATE(InputString, CHR(0)||SUBSTR(c_Alphabet, 1,36), CHR(0)) IS NULL THEN 36
WHEN c_Compare = 'A' AND TRANSLATE(InputString, CHR(0)||c_Alphabet , CHR(0)) IS NULL THEN 62
ELSE 1
END;
CASE v_base
WHEN 0 THEN RAISE BAD_COMP_TYP_EXCP;
WHEN 1 THEN RAISE BAD_INP_TXT_EXCP;
ELSE
WHILE v_increment != 0 AND v_idx > 1 LOOP
v_idx := v_idx - 1;
v_mod := MOD (v_increment, v_base);
v_weight := INSTR(c_Alphabet, SUBSTR(InputString, v_idx, 1) );
IF v_weight > v_base THEN
v_low_case := 26;
ELSE
v_low_case := 0;
END IF;
v_weight := v_weight - v_low_case + v_mod;
v_roll := 0;
IF v_weight > 10 AND v_weight <= v_base THEN
v_weight := v_weight + v_low_case;
ELSIF IncrementBy < 0 AND v_weight <= 0 THEN
v_weight := v_weight + v_base + v_low_case ;
v_roll := -1;
ELSIF IncrementBy > 0 AND v_weight > v_base THEN
v_weight := v_weight - v_base;
v_roll := 1;
END IF;
v_new_val := SUBSTR(c_Alphabet, v_weight, 1) || v_new_val;
v_increment := ((v_increment - v_mod ) / v_base) + v_roll;
END LOOP;
RETURN (SUBSTR(InputString, 1, v_idx - 1) || v_new_val);
END CASE;
--
EXCEPTION
--
WHEN BAD_INP_TXT_EXCP THEN
dbms_output.put_line(c_Err_Msg||'Input string '''||InputString
||''' contains invalid for compare type '''||CompareType||''' chars '''
||TRANSLATE(InputString, CHR(0)||c_Alphabet, CHR(0))||'''. ' );
RETURN(NULL);
WHEN BAD_COMP_TYP_EXCP THEN
dbms_output.put_line(c_Err_Msg||'Invalid compare type '''||CompareType||'''. ');
RETURN(NULL);
WHEN OTHERS THEN
dbms_output.put_line(RTRIM(DBMS_UTILITY.FORMAT_ERROR_STACK,CHR(10)));
dbms_output.put_line(RTRIM(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE,CHR(10)));
RETURN(NULL);
--
END;
Edited by: slono on Apr 19, 2010 10:08 AM