As all know I can use single quotes to start/end a string in SQL and PL/SQL.
SELECT 'my string' AS a FROM dual; A --------- my string
When we have single quotes in a string we need an escape character. In Oracle this is another single quote. That looks like this.
SELECT 'my string with two single quotes for one '' ' AS a FROM dual; A ------------------------------------------- my string with two single quotes for one '
Complex strings can be difficult to handle this way. But Oracle provides another option.
SELECT q'[my string]' AS a FROM dual; A --------- my string
This way you can have as many single quotes in the string as you wish.
There are other options besides [] – {}, ().
SELECT q'{my 1' 2'' 3''' 4'''' string}' AS a FROM dual; A --------------------------- my 1' 2'' 3''' 4'''' string
I hope this helps.