Skip to Main Content

Breadcrumb

May 4th

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

Version: 12.2.0.1.0

Viewed 1000+ 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:

'pw://pw.vd.dk:Vejdirektoratet/Documents/Bigger&space;projects/7x/Workflow.JPG'

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://pw.vd.dk:Vejdirektoratet/Documents/Bigger&space;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 Connor 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:

cs.setString(1,"pw://pw.vd.dk:Vejdirektoratet/Documents/Bigger&space;projects/7x/Workflow.JPG");


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.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

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