How to enter special characters like "&" in oracle database?
SqlDatabaseOracleOracle SqldeveloperSql Problem Overview
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.
How can I enter special characters like & in the insert statement for oracle db?
Sql Solutions
Solution 1 - Sql
If you are in SQL*Plus or SQL Developer, you want to run
SQL> 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.
Solution 2 - Sql
Try 'Java_22 '||'&'||' Oracle_14'
Solution 3 - Sql
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');
Solution 4 - Sql
you can simply escape & by following a dot. try this:
INSERT INTO STUDENT(name, class_id) VALUES ('Samantha', 'Java_22 &. Oracle_14');
Solution 5 - Sql
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.
Solution 6 - Sql
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'
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.
Solution 7 - Sql
There are 3 ways to do so :
-
Simply do SET DEFINE OFF; and then execute the insert stmt.
-
Simply by concatenating reserved word within single quotes and concatenating it. E.g. Select 'Java_22 ' || '& '|| ':' || ' Oracle_14' from dual --(:) is an optional.
-
By using CHR function along with concatenation. E.g. Select 'Java_22 ' || chr(38)||' Oracle_14' from dual
Hope this help !!!
Solution 8 - Sql
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)||'');
Solution 9 - Sql
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;
--
dōTTTT
Solution 10 - Sql
Also you can use concat like this :D
Insert into Table Value(CONCAT('JAVA ',CONCAT('& ', 'Oracle'));
Solution 11 - Sql
strAdd=strAdd.replace("&","'||'&'||'");
Solution 12 - Sql
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.
Solution 13 - Sql
You can either use the backslash character to escape a single character or symbol
> 'Java_22 & Oracle_14'
or braces to escape a string of characters or symbols
> '{Java_22 & Oracle_14}'