URL decode a column in table

Question

How can I url decode a value in Oracle?

I have a URL encoded string stored in oracle DB table. I want to url_encode it while selecting the results. Any quick way to achieve this ?

Answer 1

Oracle provides utl_url package containing two functions escape() and unescape() which allow you encode and decode urls. To decode an encoded url string http://www.%24-%26-%3C-%3E-%3F, for example, we can do the following:

SQL> select utl_url.unescape('http://www.%24-%26-%3C-%3E-%3F') as res
  2   from dual
  3  ;

Result:

RES
---------------------
http://www.$-&-<->-?

Note. If you need to use escape() function, you wont be able to use it in a select statement directly, because the second parameter of the function is of Boolean datatype. You will need to write a wrapper function.

SQL> create or replace function url_encode(p_url in varchar2)
  2  return varchar2
  3  is
  4  begin
  5    return utl_url.escape(p_url, true);
  6  end;
  7  /
Function created

SQL> 
SQL> select Url_encode('http://www.$-&-<->-?') as res
  2   from dual
  3  ;

Result:

RES
-------------------------------------
http%3A%2F%2Fwww.%24-%26-%3C-%3E-%3F