Replace each letter with it's ASCII code in a string in PL/SQL

Question

is a better, shorter way to preform this code:

/*Replace all letters by their respective ASCII code - 55*/
as_iban := REPLACE(as_iban, 'A', '10');
as_iban := REPLACE(as_iban, 'B', '11');
as_iban := REPLACE(as_iban, 'C', '12');
as_iban := REPLACE(as_iban, 'D', '13');
as_iban := REPLACE(as_iban, 'E', '14');
as_iban := REPLACE(as_iban, 'F', '15');
as_iban := REPLACE(as_iban, 'G', '16');
as_iban := REPLACE(as_iban, 'H', '17');
as_iban := REPLACE(as_iban, 'I', '18');
as_iban := REPLACE(as_iban, 'J', '19');
as_iban := REPLACE(as_iban, 'K', '20');
as_iban := REPLACE(as_iban, 'L', '21');
as_iban := REPLACE(as_iban, 'M', '22');
as_iban := REPLACE(as_iban, 'N', '23');
as_iban := REPLACE(as_iban, 'O', '24');
as_iban := REPLACE(as_iban, 'P', '25');
as_iban := REPLACE(as_iban, 'Q', '26');
as_iban := REPLACE(as_iban, 'R', '27');
as_iban := REPLACE(as_iban, 'S', '28');
as_iban := REPLACE(as_iban, 'T', '29');
as_iban := REPLACE(as_iban, 'U', '30');
as_iban := REPLACE(as_iban, 'V', '31');
as_iban := REPLACE(as_iban, 'W', '32');
as_iban := REPLACE(as_iban, 'X', '33');
as_iban := REPLACE(as_iban, 'Y', '34');
as_iban := REPLACE(as_iban, 'Z', '35');

The code above converts all the upper chars of the string to there respective ASCII code numbers. but this is not the correct way of going about it, but I can't figure out another way of doing it.

I have tried something like

FOR i in 1..LENGTH(as_iban)
LOOP
select regexp_replace(as_iban,'['||substr(as_iban,i,1)||']', ASCII(regexp_substr(as_iban,'['||substr(as_iban,i,1)||']')) - 55) into as_iban FROM dual;
END LOOP;

Solution 1

I think you might be looking for something like this:

CREATE OR REPLACE FUNCTION FUBAR_STR(in_str VARCHAR2) RETURN VARCHAR2 AS
  out_str VARCHAR2(4000) := '';
BEGIN
  FOR i IN 1..LENGTH(in_str) LOOP
    out_str := out_str || TO_CHAR(ASCII(SUBSTR(in_str,i,1)) - 55);
  END LOOP;
  RETURN out_str;
END FUBAR_STR;

So when you run:

select fubar_str('abcd') from dual;

You get: 42434445.

Here is the reversible, safer one to use.

CREATE OR REPLACE FUNCTION FUBAR_STR(in_str VARCHAR2) RETURN VARCHAR2 AS
  out_str VARCHAR2(32676) := '';
BEGIN
  FOR i IN 1..LEAST(LENGTH(in_str),10892) LOOP
    out_str := out_str || LPAD(TO_CHAR(ASCII(SUBSTR(in_str,i,1)) - 55),3,'0');
  END LOOP;
  RETURN out_str;
END FUBAR_STR;

So when you run:

select fubar_str('abcd') from dual;

You get: 042043044045.

And because I'm really bored tonight:

CREATE OR REPLACE FUNCTION UNFUBAR_STR(in_str VARCHAR2) RETURN VARCHAR2 AS
  out_str VARCHAR2(10892) := '';
BEGIN
  FOR i IN 0..(((LENGTH(in_str) - MOD(LENGTH(in_str),3))/3) - 1) LOOP
    out_str := out_str || CHR(TO_NUMBER(LTRIM(SUBSTR(in_str,(i * 3) + 1,3),'0')) + 55);
  END LOOP;
  RETURN out_str;
END UNFUBAR_STR;

So when you run:

select unfubar_str('042043044045') from dual;

You get: abcd.

Solution 2

Here's a way to do it in one command:

select listagg((column_value).getstringval() - 55) 
within group (order by 1) as output
from xmltable(regexp_replace(dump('abcd'), '.*: (\d.*)$', '\1'));

Output:

Explanation:

Take the output from dump() and use regexp_replace() to extract the comma-separated list of ASCII values from the output (Dump output includes a comma-separated list of ASCII values of the data passed in). Use xmltable() to turn that comma-separated list into rows. This is needed because we need to then process each row, subtracting 55 from the value per the requirement, using listagg() to turn the result after subtracting 55 into one string.

For more info, see:

dump(): http://docs.oracle.com/cd/B28359_01/server.111/b28286/functions048.htm#SQLRF00635 regexp_replace(): http://docs.oracle.com/cd/B28359_01/server.111/b28286/functions137.htm#SQLRF06302 xmltable(): http://docs.oracle.com/cd/B28359_01/server.111/b28286/functions240.htm#SQLRF06232 listagg(): http://docs.oracle.com/cd/E11882_01/server.112/e26088/functions089.htm#SQLRF30030