Reputation: 2276
Oracle PL/SQL Convert string into INTEGER (maximum value 2147483648)
Please suppose you have a VARCHAR2 string of 6 character so composed:
LETTER LETTER DIGIT LETTER LETTER LETTER
where LETTER = A, B, C, ....., W, X, Y, Z and NUMBER = 0, 1, 2, 3, ....., 9
The minimum string with the previous rules is: AA0AAA The maximum string with the previous rules is: ZZ9ZZZ
The number of possible combinations is:
26 * 26 * 10 * 26 * 26 * 26 = 118813760
How can I decode AA0AAA to 1, AA0AAB to 2, AA0AAC to 3, ....., ZZ9ZZZ to 118813760?
I am programming in Oracle PL/SQL.
Thank you in advance for your kind cooperation.
This is my code but it is not optimized :(
SELECT RIGA
FROM (SELECT ROWNUM riga, alfa.*
FROM (WITH s1 AS (SELECT 'A' list1 FROM DUAL
UNION ALL
SELECT 'B' FROM DUAL
UNION ALL
SELECT 'C' FROM DUAL
UNION ALL
SELECT 'D' FROM DUAL
UNION ALL
SELECT 'E' FROM DUAL
UNION ALL
SELECT 'F' FROM DUAL
UNION ALL
SELECT 'G' FROM DUAL
UNION ALL
SELECT 'H' FROM DUAL
UNION ALL
SELECT 'I' FROM DUAL
UNION ALL
SELECT 'J' FROM DUAL
UNION ALL
SELECT 'K' FROM DUAL
UNION ALL
SELECT 'L' FROM DUAL
UNION ALL
SELECT 'M' FROM DUAL
UNION ALL
SELECT 'N' FROM DUAL
UNION ALL
SELECT 'O' FROM DUAL
UNION ALL
SELECT 'P' FROM DUAL
UNION ALL
SELECT 'Q' FROM DUAL
UNION ALL
SELECT 'R' FROM DUAL
UNION ALL
SELECT 'S' FROM DUAL
UNION ALL
SELECT 'T' FROM DUAL
UNION ALL
SELECT 'U' FROM DUAL
UNION ALL
SELECT 'V' FROM DUAL
UNION ALL
SELECT 'W' FROM DUAL
UNION ALL
SELECT 'X' FROM DUAL
UNION ALL
SELECT 'Y' FROM DUAL
UNION ALL
SELECT 'Z' FROM DUAL),
s2 AS (SELECT 'A' list2 FROM DUAL
UNION ALL
SELECT 'B' FROM DUAL
UNION ALL
SELECT 'C' FROM DUAL
UNION ALL
SELECT 'D' FROM DUAL
UNION ALL
SELECT 'E' FROM DUAL
UNION ALL
SELECT 'F' FROM DUAL
UNION ALL
SELECT 'G' FROM DUAL
UNION ALL
SELECT 'H' FROM DUAL
UNION ALL
SELECT 'I' FROM DUAL
UNION ALL
SELECT 'J' FROM DUAL
UNION ALL
SELECT 'K' FROM DUAL
UNION ALL
SELECT 'L' FROM DUAL
UNION ALL
SELECT 'M' FROM DUAL
UNION ALL
SELECT 'N' FROM DUAL
UNION ALL
SELECT 'O' FROM DUAL
UNION ALL
SELECT 'P' FROM DUAL
UNION ALL
SELECT 'Q' FROM DUAL
UNION ALL
SELECT 'R' FROM DUAL
UNION ALL
SELECT 'S' FROM DUAL
UNION ALL
SELECT 'T' FROM DUAL
UNION ALL
SELECT 'U' FROM DUAL
UNION ALL
SELECT 'V' FROM DUAL
UNION ALL
SELECT 'W' FROM DUAL
UNION ALL
SELECT 'X' FROM DUAL
UNION ALL
SELECT 'Y' FROM DUAL
UNION ALL
SELECT 'Z' FROM DUAL),
k1 AS (SELECT '0' list3 FROM DUAL
UNION ALL
SELECT '1' FROM DUAL
UNION ALL
SELECT '2' FROM DUAL
UNION ALL
SELECT '3' FROM DUAL
UNION ALL
SELECT '4' FROM DUAL
UNION ALL
SELECT '5' FROM DUAL
UNION ALL
SELECT '6' FROM DUAL
UNION ALL
SELECT '7' FROM DUAL
UNION ALL
SELECT '8' FROM DUAL
UNION ALL
SELECT '9' FROM DUAL),
s3 AS (SELECT 'A' list4 FROM DUAL
UNION ALL
SELECT 'B' FROM DUAL
UNION ALL
SELECT 'C' FROM DUAL
UNION ALL
SELECT 'D' FROM DUAL
UNION ALL
SELECT 'E' FROM DUAL
UNION ALL
SELECT 'F' FROM DUAL
UNION ALL
SELECT 'G' FROM DUAL
UNION ALL
SELECT 'H' FROM DUAL
UNION ALL
SELECT 'I' FROM DUAL
UNION ALL
SELECT 'J' FROM DUAL
UNION ALL
SELECT 'K' FROM DUAL
UNION ALL
SELECT 'L' FROM DUAL
UNION ALL
SELECT 'M' FROM DUAL
UNION ALL
SELECT 'N' FROM DUAL
UNION ALL
SELECT 'O' FROM DUAL
UNION ALL
SELECT 'P' FROM DUAL
UNION ALL
SELECT 'Q' FROM DUAL
UNION ALL
SELECT 'R' FROM DUAL
UNION ALL
SELECT 'S' FROM DUAL
UNION ALL
SELECT 'T' FROM DUAL
UNION ALL
SELECT 'U' FROM DUAL
UNION ALL
SELECT 'V' FROM DUAL
UNION ALL
SELECT 'W' FROM DUAL
UNION ALL
SELECT 'X' FROM DUAL
UNION ALL
SELECT 'Y' FROM DUAL
UNION ALL
SELECT 'Z' FROM DUAL),
s4 AS (SELECT 'A' list5 FROM DUAL
UNION ALL
SELECT 'B' FROM DUAL
UNION ALL
SELECT 'C' FROM DUAL
UNION ALL
SELECT 'D' FROM DUAL
UNION ALL
SELECT 'E' FROM DUAL
UNION ALL
SELECT 'F' FROM DUAL
UNION ALL
SELECT 'G' FROM DUAL
UNION ALL
SELECT 'H' FROM DUAL
UNION ALL
SELECT 'I' FROM DUAL
UNION ALL
SELECT 'J' FROM DUAL
UNION ALL
SELECT 'K' FROM DUAL
UNION ALL
SELECT 'L' FROM DUAL
UNION ALL
SELECT 'M' FROM DUAL
UNION ALL
SELECT 'N' FROM DUAL
UNION ALL
SELECT 'O' FROM DUAL
UNION ALL
SELECT 'P' FROM DUAL
UNION ALL
SELECT 'Q' FROM DUAL
UNION ALL
SELECT 'R' FROM DUAL
UNION ALL
SELECT 'S' FROM DUAL
UNION ALL
SELECT 'T' FROM DUAL
UNION ALL
SELECT 'U' FROM DUAL
UNION ALL
SELECT 'V' FROM DUAL
UNION ALL
SELECT 'W' FROM DUAL
UNION ALL
SELECT 'X' FROM DUAL
UNION ALL
SELECT 'Y' FROM DUAL
UNION ALL
SELECT 'Z' FROM DUAL),
s5 AS (SELECT 'A' list6 FROM DUAL
UNION ALL
SELECT 'B' FROM DUAL
UNION ALL
SELECT 'C' FROM DUAL
UNION ALL
SELECT 'D' FROM DUAL
UNION ALL
SELECT 'E' FROM DUAL
UNION ALL
SELECT 'F' FROM DUAL
UNION ALL
SELECT 'G' FROM DUAL
UNION ALL
SELECT 'H' FROM DUAL
UNION ALL
SELECT 'I' FROM DUAL
UNION ALL
SELECT 'J' FROM DUAL
UNION ALL
SELECT 'K' FROM DUAL
UNION ALL
SELECT 'L' FROM DUAL
UNION ALL
SELECT 'M' FROM DUAL
UNION ALL
SELECT 'N' FROM DUAL
UNION ALL
SELECT 'O' FROM DUAL
UNION ALL
SELECT 'P' FROM DUAL
UNION ALL
SELECT 'Q' FROM DUAL
UNION ALL
SELECT 'R' FROM DUAL
UNION ALL
SELECT 'S' FROM DUAL
UNION ALL
SELECT 'T' FROM DUAL
UNION ALL
SELECT 'U' FROM DUAL
UNION ALL
SELECT 'V' FROM DUAL
UNION ALL
SELECT 'W' FROM DUAL
UNION ALL
SELECT 'X' FROM DUAL
UNION ALL
SELECT 'Y' FROM DUAL
UNION ALL
SELECT 'Z' FROM DUAL)
SELECT *
FROM S1,
S2,
K1,
S3,
S4,
S5) alfa)
WHERE LIST1 = 'A'
AND LIST2 = 'A'
AND LIST3 = '1'
AND LIST4 = 'A'
AND LIST5 = 'A'
AND LIST6 = 'A';
Upvotes: 0
Views: 1774
Answers (4)
Reputation: 11
You can use the ascii function to convert from a letter to a number. 'A' = 65 in ascii, 'Z' = 90. So substract 64 from the result and you have the correct value.
Then for the number, add 1 to it (0 maps to 1, 9 maps to 10)
see edit below, this part is not correct
Multiply all parts, and voila: this is a SQL-only solution. Much-much faster than declaring a PL/SQL function, because then for each result the DB has to switch between SQL and PL/SQL context.
select t.string input
, to_number(ascii(substr(t.string,1,1))-64)
* to_number(ascii(substr(t.string,2,1))-64)
* (to_number(substr(t.string,3,1)) + 1)
* to_number(ascii(substr(t.string,4,1))-64)
* to_number(ascii(substr(t.string,5,1))-64)
* to_number(ascii(substr(t.string,6,1))-64) as value
from (select 'AA0AAA' string from dual
union all
select 'ZZ9ZZZ' string from dual
) t;
select ascii('Z') from dual;
EDIT - improved answer I saw the answer of ProblemFactory. And he is doing it right: there is a weighing factor. I.e. AA0ZZZ should be lower than ZZ0ZAA while the version above returns the same. However, in his solution there is a need for a declared PL/SQL function, which will lead to a big performance penalty when executed for a large data set.
The following query will return the correct value (with thanks to ProblemFactory):
select t.string input
, to_number(ascii(substr(t.string,1,1))-65) * 4569760 --26*26*26*10*26
+ to_number(ascii(substr(t.string,2,1))-65) * 175760 --26*26*26*10
+ to_number(substr(t.string,3,1)) * 17576 --26*26*26
+ to_number(ascii(substr(t.string,4,1))-65) * 676 --26*26
+ to_number(ascii(substr(t.string,5,1))-65) * 26
+ to_number(ascii(substr(t.string,6,1))-64) as value
from (select 'AA0AAA' string from dual
union all
select 'ZZ9ZZZ' string from dual
) t;
results in
INPUT VALUE
------ ----------
AA0AAA 1
ZZ9ZZZ 118813760
Upvotes: 1
Reputation: 9811
Tested on SQL Developer, input is in variable in_string:
declare
in_string varchar2(20):= 'ZZ9ZZZ';
type m IS VARRAY(6) OF INTEGER;
weights m;
in_char char(1);
cnt number := 0;
res number := 1;
BEGIN
weights:= m(4569760, 175760, 17576, 676, 26, 1);
while (cnt < length(in_string))
loop
cnt := cnt + 1;
in_char := substr(in_string, cnt, 1);
if cnt != 3 then
res := res + (ASCII(in_char) - 65) * weights(cnt);
else
res := res + (ASCII(in_char) - 48) * weights(cnt);
end if;
end loop;
DBMS_OUTPUT.PUT_LINE(res);
END;
/
which gives me: 118813760.
Upvotes: 2
Reputation: 2863
If this turns out to be a hard problem in PL/SQL, there is a MixedRadixNumber java class available in aima.
It is certainly possible to call out to java from within PL/SQL, as documented in this O'Reilly book.
Upvotes: 0
Reputation: 2863
Assuming that your string format is fixed, you could break this down by writing a much simpler pl/sql function that takes in a single letter and returns the number of that letter - e.g., A=0, Z=25.
If you know what position that letter had in your string, you can work out how much that letter is worth individually, and then multiply it by the power of 26 that is appropriate to that position.
Upvotes: 0
Related Questions
- ORACLE - String to number
- PL/SQL convert string to number
- How to convert a string to number in PL/SQL
- Oracle - How to decode a string converted to a number
- Oracle SQL String to Number conversion
- Convert integer or decimal number to string
- Oracle-SQL Query to obtain the maximum value of a column that has been converted from String to Integer
- Oracle: Converting string to int
- Use max in a VARCHAR to get result > 999999?
- pl/sql conversion from number to string