How to enter special characters like “\” in oracle database INSERT STATEMENT?

Question

UPDATE: I've just figured out that the problem wasn't coming from the backslash. The problem is a single quotation mark problem. I'm trying to insert words with apostrophes, and whenever there is one in my list, the apostrophe is treated like a single quotation mark. Oracle is thus adding a backslash automatically even though I didn't have one in the original insert values.

Example:

INSERT INTO DICTIONNAIRE (Mot) VALUES ('s\'ensuivre');

was originally

INSERT INTO DICTIONNAIRE (Mot) VALUES ('s'ensuivre');

Is there a way to treat the apostrophe differently than the single quotations surrounding the values?

Original question:

I want to insert a backslash as a string:

INSERT INTO Dictionnaire (Mot,Definition) VALUES ('abasourdir','v. tr.\\n Ahurir.');

Is there a way to prevent the backslash from doing what it normally does, that is: "escaping a single character or symbol", and have the DBMS treat it like a simple string?

set define off; doesn't work.

Thanks!

Answer 1

Is there a way to treat the apostrophe differently than the single quotations surrounding the values?

INSERT INTO DICTIONNAIRE (Mot) VALUES ('s'ensuivre');

Yes, to INSERT a single quote in a value, simply double it.

INSERT INTO DICTIONNAIRE (Mot) VALUES ('s''ensuivre');

Answer 2

I've just figured out that the problem wasn't coming from the backslash. The problem is a single quotation mark problem. I'm trying to insert words with apostrophes, and whenever there is one in my list, the apostrophe is treated like a single quotation mark. Oracle is thus adding a backslash automatically even though I didn't have one in the original insert values.

Example:

INSERT INTO DICTIONNAIRE (Mot) VALUES ('s\'ensuivre');

was originally

INSERT INTO DICTIONNAIRE (Mot) VALUES ('s'ensuivre');

Is there a way to treat the apostrophe differently than the single quotations surrounding the values?

Answer 3

I want to insert a backslash as a string: INSERT INTO Dictionnaire (Mot,Definition) VALUES ('abasourdir','v. tr.\n Ahurir.');

Backslash will work as escape character only if you set the escape character as backslash, or use the ESCAPE command in the individual SQL statement. Lest you should be able to insert it like any other character.

Use two backslashes instead of a single backslash.

For example, I have set the escape character as set escape '\':

SQL> CREATE TABLE t(a number, b VARCHAR2(10));

Table created.

SQL>
SQL> set escape '\'
SQL>
SQL> INSERT INTO t VALUES(1, '\\');

1 row created.

SQL> INSERT INTO t VALUES(2, '\\n');

1 row created.

SQL>
SQL> SELECT * FROM t;

         A B
---------- ----------
         1 \
         2 \n

SQL>

Is there a way to prevent the backslash from doing what it normally does, that is: "escaping a single character or symbol", and have the DBMS treat it like a simple string?

Then don't use backslash to escape. Set any other character as an escape character.

For example, I will use forward slash as escape character instead of backslash:

SQL> DROP TABLE t PURGE;

Table dropped.

SQL>
SQL> CREATE TABLE t(a number, b VARCHAR2(10));

Table created.

SQL>
SQL> set escape '/'
SQL>
SQL> INSERT INTO t VALUES(1, '\');

1 row created.

SQL> INSERT INTO t VALUES(2, '\n');

1 row created.

SQL> INSERT INTO t VALUES(2, '/n');

1 row created.

SQL>
SQL> SELECT * FROM t;

         A B
---------- ----------
         1 \
         2 \n
         2 n

SQL>

So, as you can see, only the forward slash was used to escape, not the backslash.

For that matter, you could use any other character to escape as well:

SQL> set escape '#'
SQL>
SQL> INSERT INTO t VALUES(1, '\');

1 row created.

SQL> INSERT INTO t VALUES(2, '\n');

1 row created.

SQL> INSERT INTO t VALUES(2, '#n');

1 row created.

SQL>
SQL> SELECT * FROM t;

         A B
---------- ----------
         1 \
         2 \n
         2 n

SQL>

So, you can see that '#' was used as an escape character. Backslash had no affect.

Answer 4

SQL> CREATE TABLE Dictionnaire(MOT VARCHAR2(20),Definition varchar2(40));

Table created.

Elapsed: 00:00:00.71

This is if you want the quotes also, if you want just the back slash then see other insert,

SQL> INSERT INTO Dictionnaire (Mot,Definition) VALUES ('abasourdir','v. tr.''\''n Ahurir.');

1 row created.


Elapsed: 00:00:00.01
SQL> select * from Dictionnaire ;

MOT                  DEFINITION
-------------------- ----------------------------------------
abasourdir           v. tr.'\'n Ahurir.

Elapsed: 00:00:00.01


SQL> INSERT INTO Dictionnaire (Mot,Definition) VALUES ('abasourdir','v. tr.\n Ahurir.');

1 row created.

Elapsed: 00:00:00.01
SQL> select * from Dictionnaire ;

MOT                  DEFINITION
-------------------- ----------------------------------------
abasourdir           v. tr.'\'n Ahurir.
abasourdir           v. tr.\n Ahurir.

Elapsed: 00:00:00.01