CODEWITHSUNDEEP
databaseoracle-databaseplsqlbase64
Kinjan Bhavsar
Kinjan Bhavsar

Reputation: 1449

How to remove '\r\n' from Base64 string in oracle?

I have created the following function in Oracle to convert my string to BASE64 and I am getting a new line after 64 characters. The function is as below

function to_base64(t in varchar2) return varchar2 is
begin
  return utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw(t)));
end to_base64;

My input is tobase64('ACae23b41949dbcfa8cd2c8b114bb81af4:909f2edb3e9b8c7e9c545d2942aa8576')

and output I am getting is as follows where new line is added after 64 characters. QUNhZTIzYjQxOTQ5ZGJjZmE4Y2QyYzhiMTE0YmI4MWFmNDo5MDlmMmVkYjNlOWI4 YzdlOWM1NDVkMjk0MmFhODU3Ng==

Can someone suggest how can I remove the new line or any change required in the function?

Upvotes: 4

Views: 3736

Answers (1)

Alex Poole
Alex Poole

Reputation: 191435

You can use replace() to remove the two characters individually:

function to_base64(t in varchar2) return varchar2 is
begin
  return replace(
    replace(
      utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw(t))),
      chr(10)),
    chr(13)
  );
end to_base64;

or as a pair:

function to_base64(t in varchar2) return varchar2 is
begin
  return replace(
    utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw(t))),
    chr(13)||chr(10)
  );
end to_base64;

and then either way

select to_base64('ACae23b41949dbcfa8cd2c8b114bb81af4:909f2edb3e9b8c7e9c545d2942aa8576')
from dual;

gives the result on a single line:

QUNhZTIzYjQxOTQ5ZGJjZmE4Y2QyYzhiMTE0YmI4MWFmNDo5MDlmMmVkYjNlOWI4YzdlOWM1NDVkMjk0MmFhODU3Ng==

But it's common for base64 values to include them, and decoding should ignore them anyway.

Upvotes: 9

Related Questions