string - Replace Non-Alphanumeric Characters in Oracle -

i trying convert string in oracle modified string compatible specific api.

i leave alphanumeric characters intact, replace spaces + character, , replace special characters % plus hex code.

for example,

project 1: nuts & bolts

should become

project+1%3a+nuts+%26+bolts

is there way using sql?

i don't think can there plain sql without nested replace calls. can sample value utl_url.escape() function, because have pass second parameter , boolean, have in pl/sql block:

set define off begin   dbms_output.put_line(replace(utl_url.escape('project 1: nuts & bolts', true),     '%20', '+')); end; /  project+1%3a+nuts+%26+bolts 

the url_utl.escape function converts spaces %20:

project%201%3a%20nuts%20%26%20bolts 

... , single replace call converts +.

as ed gibbs said, can make function can @ least call plain sql:

create or replace function my_escape(str in varchar2) return varchar2 begin     return replace(utl_url.escape(str, true), '%20', '+'); end; /  set define off select my_escape('project 1: nuts & bolts') dual;  my_escape('project1:nuts&bolts') -------------------------------- project+1%3a+nuts+%26+bolts 

you need check legal , reserved character lists see if there's else needs special handling.

(i've used set define off stop client treating ampersand substitution variable; client or application might not need that, e.g. if calling on jdbc).