Since I sometimes have to filter for Strings, which might contain ampassants I was browsing google to find a solution for escaping ampasant in SQL Developer.

Unfortunately the solution on the Oracle documentation using escape characters like \ or {} did not work maybe because of some invalid settings.

Finally I found a way.

SQL

If you want to escape an ampassant in a String, just replace it with the following String.

'||'&'||'

1
SELECT * FROM dual WHERE dummy = 'huhu '||'&'||' bla bla ';

Basically this just applies a concatenation with an ampassant in a dedicated String as you can see in above SQL.

Alternatively you can use the following String as a replacement.

||chr(38)||

Same principle.

Enjoy.

References

https://asktom.oracle.com/pls/asktom/f?p=100:11:0::NO::P11_QUESTION_ID:4549764300346084350