CODEWITHSUNDEEP
oracle-databaseurldecode
TopCoder
TopCoder

Reputation: 4296

URL decode a column in table

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 ?

Upvotes: 8

Views: 13564

Answers (2)

Oranit Dar
Oranit Dar

Reputation: 1735

This worked for me:

utl_url.unescape(replace('your text here', '+', ' '),'UTF-8')

Upvotes: 3

Nick Krasnov
Nick Krasnov

Reputation: 27251

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

Upvotes: 7

Related Questions