Explain Codes LogoExplain Codes Logo

Oracle SQL escape character (for an '&')

sql
sql-escape
oracle-sql
substitution-variables
Alex KataevbyAlex Kataev·Oct 31, 2024
TLDR

Escape an & in Oracle SQL using the concatenation (||) operation:

-- "Employee&Manager", but make it Oracle SELECT 'Employee' || chr(38) || 'Manager' AS job_title FROM dual;

Alternatively, disable substitution variables completely:

-- No more Mr. & guy SET DEFINE OFF;

Take note, the above methods will prevent Oracle SQL from misinterpreting & as a substitution variable prompt, enabling you to comfortably insert it into your database.

Why does & mess with my data?

In Oracle SQL, the & character triggers the substitution variables process. Here are two workarounds to manage this quirk:

  1. The concatenation method utilizes chr(38) to output & without inviting annoying substitution prompts:
-- "part1&part2", but politely INSERT INTO table_name (column_name) VALUES ('part1' || chr(38) || 'part2');
  1. SET DEFINE OFF puts the substitution mechanism to sleep:
-- The & Whisperer SET DEFINE OFF; INSERT INTO table_name (column_name) VALUES ('URL with & character here'); SET DEFINE ON; -- Wakey wakey, eggs and bac-y

The preferred method is SET DEFINE OFF for managing multiple ampersand encounters in your SQL scripts.

Seeking & asylum - Alternatives and precautions

Swap the suspect

By resetting the substitution character using SET DEFINE, you can prevent disruption when switching off is not an option:

-- Dollars > Ampersands SET DEFINE $; INSERT INTO table_name (column_name) VALUES ('part1$part2');

Don't forget, SET DEFINE has a scope and can trickle down to subsequent SQL statements in your script. Tread cautiously.

& cross-dressers in SQL Developer

SQL Developer isn't a fan of escape characters like \. It does, however, fancy other getups like 'amp;':

-- &'s day out as "amp" INSERT INTO table_name (column_name) VALUES ('part1amp;part2');

Reverting 'amp;' back to & may demand extra processing while retrieving data from the database.

The hardcoded approach

In dire situations, hardcoded values can help steer clear of escape character issues:

-- Enforced & discipline INSERT INTO table_name (column_name) VALUES ('The Cat & The Hat');

Application code string builders can incorporate special characters without sparking any SQL alerts.

Decoding the & mysteries

User input in dynamic SQL

Suppress prompts in dynamic SQL or user inputs by pre-processing the input to replace & with chr(38). Kindly note, such a tactic works without meddling with the session settings:

-- Replace & with a friendlier character v_user_input VARCHAR2(100); v_safe_input VARCHAR2(100); BEGIN v_user_input := 'Peek & Cloppenburg'; -- Iron Man's lesser-known clothing brand? v_safe_input := REPLACE(v_user_input, '&', chr(38)); EXECUTE IMMEDIATE 'INSERT INTO table_name (column_name) VALUES (:1)' USING v_safe_input; END;

Sticky substitution variables

If SET DEFINE OFF doesn't persist, always renew it at the beginning of every procedure or consider running scripts where it reigns supreme as a default setting.

Playing safe with functions and procedures

While chalk and cheese, make sure the & character in your function or procedure definitions is handled properly if these blocks pack static SQL statements containing ampersands:

-- Keeping a straight face amidst the "amphibious" & jokes CREATE OR REPLACE FUNCTION get_department_name(p_dept_id NUMBER) RETURN VARCHAR2 IS l_dept_name VARCHAR2(100); BEGIN SET DEFINE OFF; SELECT department_name INTO l_dept_name FROM departments WHERE department_id = p_dept_id AND department_name LIKE '%&%'; SET DEFINE ON; RETURN l_dept_name; END;

Always run your tests, validate them and ensure that the logic does not alter the intended meaning of your PL/SQL art.