Escaping ampersand character in SQL string

SQL*Plus: Don't ask, just do!

In Oracle's SQL*Plus, ampersands are substitution variables by default, and might ask for your input when you least expect it! To avoid this, run:

SET DEFINE OFF; -- Turning off those unexpected input prompts.


This lets the ampersand chill and behave like a normal character in your strings.

Wildcard party with LIKE operator

Searching for patterns that include an ampersand? Use the LIKE operator and the "wild child", underscore _, as a single-character wildcard:

SELECT Name FROM Employees WHERE Name LIKE 'A%&_Smith'; -- Any character can join the party in place of '&'


This fetches names that start with 'A', hold any amped-up character followed by '_Smith'.

The secret handshake: String concatenation using ||

If complex string construction is your jam, use the ||, the secret handshake for string concatenation, and usher in ampersands as needed:

SELECT 'A'||CHR(38)||'B' AS ConcatenatedString FROM dual; -- A handshaky hello to SQL strings!


Oracle nuance central: Backslashes & Escape mechanisms

Backslashes (\) may seem like a good idea, but Oracle SQL thinks differently. Try set define off or set escape on instead:

-- After setting the escape character
SELECT 'A \& B' AS EscapedString FROM dual; -- Backslashes, escape and Oracle walk into a bar...


Quick-fire tips & tricks

Know thy environment

SQL context matters. set define off works in SQL*Plus, but not other tools like SQL Developer. Check your Preferences in these cases.

Test, test, and test again

Validate your SQL syntax after applying escape sequences. It's like your code's final health check before it goes out into the world.

PL/SQL considerations

Working with dynamic SQL or PL/SQL? Be vigilant about quoting string literals properly to fend off errors or SQL injection.

Read, re-read your database docs

Each database has its special escape mechanisms. It's important to refer to the relevant documentation like a developer's version of encyclopedia!