Varchar Sequence Index Finder Oracle

Please refer how to generate varchar sequene in oracle? Prog. Findindgs.

As discussed in earlier post, that varchar sequence generator helps to generate character or string sequence very similar to number sequence in oracle.
Varchar sequence: A, B, C … Z, AA, AB, AC….AZ, BA, BC …BZ…ZA, ZB… ZZ, AAA, AAB…ZZZ, AAAA…..
This post is to do vise versa.
That is to get the sequence index value when user gives the varchar value.

Input	Output
A	1
Z	26
AZ	52
GCPB	125478
ZA	677
ZZZ	18278
AAAA	18279

Oracle function:-
CREATE OR REPLACE FUNCTION GetIndexOfStr(argVal VARCHAR)
RETURN NUMBER
IS
tmpIndex NUMBER;
tmpLength NUMBER;
BEGIN
--Getting the length of given varchar value
tmpLength:=LENGTH(argVal);
tmpIndex:=0;
--Calculating the index of the given varchar value in sequence
--A, B, C … Z, AA, AB, AC….AZ, BA, BC …BZ…ZA, ZB… ZZ, AAA, AAB…ZZZ, AAAA…..
FOR i IN 1 .. tmpLength
LOOP
tmpIndex:=tmpIndex+(ASCII(SUBSTR(argVal,i,1))-64)*POWER(26,tmpLength-i);
END LOOP;
--returning the given varchar index in the sequence
RETURN tmpIndex;
END;
Output:-
Run below query
SELECT GetIndexOfStr('ZZZ') as Sequence_Index FROM dual
Which will return the Index of ‘ZZZ’ in the considered varchar sequence
A, B, C … Z, AA, AB, AC….AZ, BA, BC …BZ…ZA, ZB… ZZ, AAA, AAB…ZZZ, AAAA…..
Result is 18278, since Index of 'ZZZ' in the considered varchar sequence is
18278