Reputation: 324
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!
Upvotes: 1
Views: 38193
Reputation: 57843
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');
Upvotes: 1
Reputation: 324
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?
Upvotes: 0
Reputation: 49122
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.
Upvotes: 2
Reputation: 1132
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
Upvotes: 1