Skip to Main Content


Question and Answer

Connor McDonald

Thanks for the question, Kåre.

Asked: June 03, 2021 - 7:14 am UTC

Last updated: June 08, 2021 - 3:49 am UTC


Viewed 100+ times

You Asked

I have an input string I can't change.

Unfortunately this input string contains an ampersand (&). I was hoping to be able to create a function which could translate an URL into something more readable text.

The input string will change but an example could be as following:


and in this case the wanted output string would be as following

'Vejdirektoretet\Documents\Bigger projects\7x\Workflow.JPG'

My hope is to be able to create a function URL2PATH which could be called as:

select URL2PATH('pw://;projects/7x/Workflow.JPG') from dual;

with the wanted result mentioned above.

If that is not possible when the input string contains an ampersand, I secondary would like a workaround to call the function with some additional addons but I haven't been able to figure out any solution for that either. I have been looking at REPLACE, TO_CHAR, TRANSLATE etc. but until now without any succes. One of my challenges is, that this function has to be called from an outside program, so the solution by using 'set define off' ahead of the call doesn't seems to be an option.

and we said...

The "&" is a facility provided by client programs (eg SQLcl, SQLPlus) etc. It really has no bearing on the database itself.

So as long as your calling program can pass in that URL as a bind variable (as it should) then you should be fine.

For example, a function such as:

SQL> create or replace
  2  function url2path(p_url varchar2) return varchar2 is
  3  begin
  4    return upper(p_url);
  5  end;
  6  /

Function created.

would be called from (say) Java as:

CallableStatement cs = con.prepareCall("{? = call url2path(?)}");

and the binding would be something like:


and Java is not going to care about the ampersand (and neither will most other languages).

Ampersand has no significance to the database, only to the client programs that access it.


  (1 rating)


For SQL*Plus

A reader, June 08, 2021 - 7:32 pm UTC

If you want to turn off the ampersand processing in sqlplus or sql developer, you can also issue a “set define off” first.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library