Oracle - How to use a function inside an escape with q-CodePudding

I know that I can escape strings in a statement like this:

select 'That''s a really funny ''joke''' from dual; --returns: That's a really funny 'joke'

Or like this:

select q'[That's a really funny 'joke']' from dual; --returns: That's a really funny 'joke'

Both are working fine.

Let's say I need to escape a string exactly in that select statement and I also need to use a function.

select q'[myfunction(somestringvariable)]' from dual;

Of course this only returns "myfunction(somestringvariable)"

As said, I need to escape the results of the function (can't be done inside the function, escape needs to happen in this select statement).

Can I use the function somehow inside a string escaped with "q"?

Thanks!

CodePudding user response：

To double single quotes in the function result:

REPLACE(myfunction(somestringvariable), '''', '''''')

CodePudding user response：

Oracle does not support template literals.

Just use:

SELECT myfunction(somestringvariable) FROM DUAL;

or, if the function does not return a string:

SELECT TO_CHAR(myfunction(somestringvariable)) FROM DUAL;

If you want to concatenate a quoted string literal and a function result then use string concatenation.

SELECT q'[That's a really funny 'joke']'
       || myfunction(somestringvariable)
FROM   DUAL;