How to enter special characters like in oracle database

Question

I want to insert special character  amp  in my insert statement  My insert is   INSERT INTO STUDENT name  class id  VALUES   Samantha    Java 22  amp  Oracle 14      If I try to run this query I am getting a popup and it asks me to enter value for Oracle 14    How can I enter special characters like  amp  in the insert statement for oracle db

User · Answer

In my case I need to insert a row with text 'Please dial *001 for help'. In this case the special character is an asterisk.

By using direct insert using sqlPlus it failed with error "SP2-0734: unknown command beginning ... "

I tryed set escape without success.

To achieve, I created a file insert.sql on filesystem with

insert into testtable (testtext) value ('Please dial *001 for help');

Then from sqlPlus I executed

@insert.sql

And row was inserted.

User · Answer

For special character set  you need to check UNICODE Charts  After choose your character  you can use sql statement below   SELECT COMPOSE  do     UNISTR   0304      TTTT    FROM dual    --  doTTTT

User · Answer

To Insert values which has got   amp   in it  Use the folloiwng code   Set define off   Begin  INSERT INTO STUDENT name  class id  VALUES   Samantha    Java 22  amp  Oracle 14     End     And Press F5 from Oracle or Toad Editors

User · Answer

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   INSERT INTO STUDENT name  class id  VALUES   Samantha    Java 22    chr 38     Oracle 14

User · Answer

Also you can use concat like this  D  Insert into Table Value CONCAT  JAVA   CONCAT   amp      Oracle

User · Answer

Try  Java 22      amp      Oracle 14

User · Answer

strAdd strAdd replace   amp         amp

User · Answer

We can use another way as well for example to insert the value with special characters  Java 22  amp  Oracle 14  into db we can use the following format      Java 22      amp      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

User · Answer

There are 3 ways to do so    1  Simply do SET DEFINE OFF  and then execute the insert stmt   2  Simply by concatenating reserved word within single quotes and concatenating it  E g  Select  Java 22        amp               Oracle 14  from dual --    is an optional   3  By using CHR function along with concatenation  E g  Select  Java 22      chr 38     Oracle 14  from dual  Hope this help

User · Answer

you can simply escape  amp  by following a dot  try this   INSERT INTO STUDENT name  class id  VALUES   Samantha    Java 22  amp   Oracle 14

User · Answer

You can either use the backslash character to escape a single character or symbol       Java 22   amp  Oracle 14    or braces to escape a string of characters or symbols        Java 22  amp  Oracle 14

User · Answer

If you are in SQL Plus or SQL Developer  you want to run  SQL gt  set define off    before executing the SQL statement   That turns off the checking for substitution variables   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

User · Answer

If an escape character is to be added at the beginning or the end like  JAVA   then use   INSERT INTO STUDENT name  class id  VALUES   Samantha       chr 34    JAVA   chr 34

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

Examples related to sql

Examples related to database

Examples related to oracle

Examples related to oracle-sqldeveloper