How to enter special characters like "&" in oracle database?

sql

parameterization

bind-variables

sqlplus

byAlex Kataev·Jan 9, 2025

To insert an & into an Oracle Database, you can either escape it with a double ampersand && or use the CHR function:

-- This is a simple way... like a Zen way.
INSERT INTO table_name (column_name) VALUES ('Text1 && Text2');
-- or
-- CHR(38), because even '&' deserves to be number one sometimes
INSERT INTO table_name (column_name) VALUES ('Text1' || CHR(38) || 'Text2');

The double ampersand && helps bypass SQL*Plus interpretation, while CHR(38) is a sure-shot ASCII method that's compatible across different interfaces.

Use of "SET DEFINE OFF" in SQL*Plus

SQL*Plus and SQL Developer recognize & as a substitution variable, which could lead to unpleasant surprises. A one-line magic trick set define off to the rescue!

SET DEFINE OFF; -- Let's give '&' a break, shall we?

This command instructs SQL*Plus to turn a blind eye to &. The setting remains only for the session and leaves other sessions undisturbed.

Smarter handling: Concatenation and parameterization

Stick pieces together using a double pipe || and run a smooth operation with parameterized queries.

-- Part 1, part 2, and a wild '&' in between!
INSERT INTO table_name (column_name) VALUES ('part1' || :specialCharacter || 'part2');

Here :specialCharacter is a bind variable and works well with special characters, even the notorious &.

Testament to correctness: Testing your code

Always validate the correctness of special characters' insertion by testing. Verify the effects of set define off across tools and ensure single-quote enclosed strings are handled properly. Remember, in Oracle, F5 is your friend!

Survival guide: Best practices and traps

Ensure your string values are always within single quotes. Evaluate client tool settings and get acquainted with specific session configurations.

When you befriend CHR()

The CHR() function comes in handy when inserting non-printable characters. Find the correct ASCII value and use CHR() for a trouble-free ride.

INSERT INTO table_name (column_name) VALUES ('CHA' || CHR(38) || 'R'); -- Ta-da! You've spelled '&amp;' right!

explain-codes / Sql / How to enter special characters like "&" in oracle database?

Linked

Oracle SQL escape character (for an '&')



How do you strip a character out of a column in SQL Server?



How to declare variable and use it in the same Oracle SQL script?



How do I ignore ampersand characters in a SQL script running from SQL Plus?



Mysql variable format for a "NOT IN" list of values



Error SQL70001: This statement is not recognized in this context



Select multiple columns into multiple variables



Use of "SET DEFINE OFF" in SQL*Plus Smarter handling: Concatenation and parameterization Testament to correctness: Testing your code Survival guide: Best practices and traps When you befriend CHR()