sql - 将每个字母替换为 PL/SQL 中字符串中的 ASCII 代码

Question

是执行此代码的更好、更短的方法:

/*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');

上面的代码将字符串的所有高位字符转换为相应的 ASCII 代码数字。但这不是正确的方法，但我想不出另一种方法。

我试过类似的东西

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;

Answer 1

我想您可能正在寻找这样的东西:

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;

所以当你运行时:

select fubar_str('abcd') from dual;

你得到:42434445。

这是可逆的，使用起来更安全。

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;

所以当你运行时:

select fubar_str('abcd') from dual;

你得到:042043044045。

因为我今晚真的很无聊:

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;

所以当你运行时:

select unfubar_str('042043044045') from dual;

你得到:abcd。