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.
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.