SQL 如何在oracle数据库中输入“&”等特殊字符？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4175431/
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?
提问by Rachel
I want to insert special character &in my insert statement. My insert is:
我想&在插入语句中插入特殊字符。我的插入是：
INSERT INTO STUDENT(name, class_id) VALUES ('Samantha', 'Java_22 & Oracle_14');
If I try to run this query I am getting a popup and it asks me to enter value for Oracle_14.
如果我尝试运行此查询，我会收到一个弹出窗口，它要求我输入Oracle_14.
How can I enter special characters like &in the insert statement for oracle db?
如何&在 oracle db 的插入语句中输入特殊字符？
回答by Justin Cave
If you are in SQL*Plus or SQL Developer, you want to run
如果您使用 SQL*Plus 或 SQL Developer，您想运行
SQL> set define off;
before executing the SQL statement. That turns of the checking for substitution variables.
在执行 SQL 语句之前。这轮到检查替代变量。
SET directives like this are instructions for the client tool (SQL*Plus or SQL Developer). They have session scope, so you would have to issue the directive every time you connect (you can put the directive in your client machine's glogin.sql if you want to change the default to have DEFINE set to OFF). There is no risk that you would impact any other user or session in the database.
像这样的 SET 指令是针对客户端工具（SQL*Plus 或 SQL Developer）的指令。它们具有会话范围，因此您必须在每次连接时发出指令（如果您想更改默认设置以将 DEFINE 设置为 OFF，您可以将指令放在客户端机器的 glogin.sql 中）。不存在影响数据库中任何其他用户或会话的风险。
回答by Eternal Noob
Try 'Java_22 '||'&'||' Oracle_14'
尝试 'Java_22 '||'&'||' Oracle_14'
回答by Craig
Justin's answer is the way to go, but also as an FYI you can use the chr() function with the ascii value of the character you want to insert. For this example it would be:
贾斯汀的答案是要走的路，但作为一个仅供参考，您可以将 chr() 函数与要插入的字符的 ascii 值一起使用。对于此示例，它将是：
INSERT INTO STUDENT(name, class_id) VALUES ('Samantha', 'Java_22 '||chr(38)||' Oracle_14');
回答by Jerry
you can simply escape & by following a dot. try this:
你可以简单地通过跟随一个点来逃避 & 。尝试这个：
INSERT INTO STUDENT(name, class_id) VALUES ('Samantha', 'Java_22 &. Oracle_14');
回答by Chikku Jacob
To Insert values which has got '&' in it. Use the folloiwng code.
插入包含“&”的值。使用以下代码。
Set define off;
Begin
INSERT INTO STUDENT(name, class_id) VALUES ('Samantha', 'Java_22 & Oracle_14');
End ;
And Press F5 from Oracle or Toad Editors.
然后在 Oracle 或 Toad 编辑器中按 F5。
回答by Ender Akdo?an
For special character set, you need to check UNICODE Charts. After choose your character, you can use sql statement below,
对于特殊字符集，您需要查看 UNICODE Charts。选择你的角色后，你可以使用下面的sql语句，
SELECT COMPOSE('do' || UNISTR('04' || 'TTTT')) FROM dual;
--
——
dōTTTT
d ōTTTT
回答by Luis Cardoza Bird
Also you can use concat like this :D
你也可以像这样使用 concat :D
Insert into Table Value(CONCAT('JAVA ',CONCAT('& ', 'Oracle'));
回答by lakhcoool
We can use another way as well for example to insert the value with special characters 'Java_22 & Oracle_14' into db we can use the following format..
我们也可以使用另一种方式，例如将带有特殊字符 'Java_22 & Oracle_14' 的值插入到数据库中，我们可以使用以下格式..
'Java_22 '||'&'||' Oracle_14'
Though it consider as 3 different tokens we dont have any option as the handling of escape sequence provided in the oracle documentation is incorrect.
尽管它认为是 3 个不同的标记，但我们没有任何选择，因为 oracle 文档中提供的转义序列的处理是不正确的。
回答by Surya
If an escape character is to be added at the beginning or the end like "JAVA", then use:
如果要在开头或结尾添加转义字符，例如"JAVA"，则使用：
INSERT INTO STUDENT(name, class_id) VALUES ('Samantha', ''||chr(34)||'JAVA'||chr(34)||'');
回答by SurajSingh444
There are 3 ways to do so :
有 3 种方法可以这样做：
1) Simply do SET DEFINE OFF; and then execute the insert stmt.
1) 只需执行 SET DEFINE OFF; 然后执行插入stmt。
2) Simply by concatenating reserved word within single quotes and concatenating it. E.g. Select 'Java_22 ' || '& '|| ':' || ' Oracle_14' from dual --(:) is an optional.
2) 只需在单引号内连接保留字并将其连接起来。例如选择'Java_22' || '&'|| ':' || 'Oracle_14' from dual --(:) 是可选的。
3) By using CHR function along with concatenation. E.g. Select 'Java_22 ' || chr(38)||' Oracle_14' from dual
3) 通过使用 CHR 函数和串联。例如选择'Java_22' || chr(38)||' Oracle_14' 来自双
Hope this help !!!
希望这有帮助！！！
