SQL 如何在oracle数据库INSERT STATEMENT中输入“\”等特殊字符？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/29934185/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to enter special characters like “\” in oracle database INSERT STATEMENT?
提问by Vincent Labrecque
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.
更新：我刚刚发现问题不是来自反斜杠。问题是单引号问题。我试图插入带有撇号的单词，每当我的列表中有一个单词时，撇号就被视为单引号。因此，即使我在原始插入值中没有反斜杠，Oracle 也会自动添加反斜杠。
Example:
例子：
INSERT INTO DICTIONNAIRE (Mot) VALUES ('s\'ensuivre');
INSERT INTO DICTIONNAIRE (Mot) VALUES ('s\'ensuivre');
was originally
原本是
INSERT INTO DICTIONNAIRE (Mot) VALUES ('s'ensuivre');
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 backslashfrom 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!
原问题：
我想插入一个反斜杠作为字符串：
INSERT INTO Dictionnaire (Mot,Definition) VALUES ('abasourdir','v. tr.\n Ahurir.');有没有办法防止反斜杠做它通常做的事情，即：“转义单个字符或符号”，并让 DBMS 将其视为一个简单的字符串？
设置定义关闭；不起作用。
谢谢！
回答by Lalit Kumar B
I want to insert a backslash as a string: INSERT INTO Dictionnaire (Mot,Definition) VALUES ('abasourdir','v. tr.\n Ahurir.');
我想插入一个反斜杠作为字符串： 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.
仅当您将转义字符设置为反斜杠或在单个 SQL 语句中使用ESCAPE 命令时，反斜杠才会用作转义字符。以免您应该能够像插入任何其他字符一样插入它。
Use two backslashesinsteadof a single backslash.
使用两个反斜杠而不是一个反斜杠。
For example, I have set the escape character as set escape '\':
例如，我将转义字符设置为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?
有没有办法防止反斜杠做它通常做的事情，即：“转义单个字符或符号”，并让 DBMS 将其视为一个简单的字符串？
Then don't use backslash to escape. Set any other characteras 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.
所以，你可以看到它'#'被用作转义字符。反斜杠没有影响。
回答by anudeepks
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
回答by Aaron
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');
回答by Vincent Labrecque
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.
我刚刚发现问题不是来自反斜杠。问题是单引号问题。我试图插入带有撇号的单词，每当我的列表中有一个单词时，撇号就被视为单引号。因此，即使我在原始插入值中没有反斜杠，Oracle 也会自动添加反斜杠。
Example:
例子：
INSERT INTO DICTIONNAIRE (Mot) VALUES ('s\'ensuivre');
INSERT INTO DICTIONNAIRE (Mot) VALUES ('s\'ensuivre');
was originally
原本是
INSERT INTO DICTIONNAIRE (Mot) VALUES ('s'ensuivre');
INSERT INTO DICTIONNAIRE (Mot) VALUES ('s'ensuivre');
Is there a way to treat the apostrophe differently than the single quotations surrounding the values?
有没有办法将撇号与围绕值的单引号区别对待？
