URL decodes a column in a table

Question

How can I decode a value in Oracle?

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

Answer 1

Oracle provides a utl_url package containing two escape () and unescape () functions that allow you to encode and decode url s. For example, to decode the encoded string url http://www.%24-%26-%3C-%3E-%3F , 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 the escape() function, you cannot use it directly in the select statement, because the second parameter of the function is of type 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