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

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

is better, shorter way preform code:

/*replace letters 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 upper chars of string there respective ascii code numbers. not right way of going it, can't figure out way of doing it.

i have tried like

for 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) as_iban dual; end loop;

i think might looking this:

create or replace function fubar_str(in_str varchar2) homecoming varchar2 out_str varchar2(4000) := ''; begin in 1..length(in_str) loop out_str := out_str || to_char(ascii(substr(in_str,i,1)) - 55); end loop; homecoming out_str; end fubar_str;

so when run:

select fubar_str('abcd') dual;

you get: 42434445.

here reversible, safer 1 use.

create or replace function fubar_str(in_str varchar2) homecoming varchar2 out_str varchar2(32676) := ''; begin 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; homecoming out_str; end fubar_str;

so when run:

select fubar_str('abcd') dual;

you get: 042043044045.

and because i'm bored tonight:

create or replace function unfubar_str(in_str varchar2) homecoming varchar2 out_str varchar2(10892) := ''; begin 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; homecoming out_str; end unfubar_str;

so when run:

select unfubar_str('042043044045') dual;

you get: abcd.

sql plsql ascii