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

Question

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?

Which tool are you using to perform the insert? – SimonJ Nov 14 '10 at 00:25 — SimonJ, Nov 14 '10 at 00:25

score 84 · Accepted Answer · edited Jun 05 '20 at 18:14

84

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.

edited Jun 05 '20 at 18:14

qyb2zm302

6,458
2
17
17

answered Nov 14 '10 at 00:13

Justin Cave

227,342
24
367
384

10

17 seconds... I was *so* close – OMG Ponies Nov 14 '10 at 00:14
1

so will this only turn defination off for my sql or for the complete table or complete database also can i have something like this in production insert script ? – Rachel Nov 14 '10 at 00:14
IMO, your production code should be *using* substitution, or at minimum, handling it itself. I believe that code does the setting for your login session, though. – Andrew Barber Nov 14 '10 at 00:23
@Rachel - SET commands are client directives that have session scope. The database has no idea what directives, if any, you've given your client so it has no impact on a table or on any other database session. – Justin Cave Nov 14 '10 at 00:32
3

@OMG Ponies- Sweet, sweet vengeance for all the times you've beaten me :-) – Justin Cave Nov 14 '10 at 00:33
@Justin: but in that case, lets say if am running the insert script with set directive off in production than will it be only for one insert or for the complete list of inserts for that particular session, can explain a little more on this, am only concern is `should i have set directive off in the insert script which will be running on production ?` – Rachel Nov 14 '10 at 00:36
2

@Rachel - It's perfectly fine to SET DEFINE OFF in a production script, yes. It will affect all the other statements running in the same session (you could, of course, SET DEFINE ON later in the script if you wanted to). But it would generally be unusual to have substitution variables in a script running in production-- you normally don't want production scripts to prompt the user for information, you normally want that to be part of the script. – Justin Cave Nov 14 '10 at 01:44
@Justin: Thanks Justin for Clarification. – Rachel Nov 14 '10 at 14:54

score 26 · Answer 2 · edited Jul 18 '15 at 14:42

26

Try 'Java_22 '||'&'||' Oracle_14'

edited Jul 18 '15 at 14:42

assylias

321,522
82
660
783

answered Nov 14 '10 at 00:16

Eternal Noob

2,717
5
27
41

score 22 · Answer 3 · answered Nov 14 '10 at 03:28

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');

score 7 · Answer 4 · answered Sep 06 '17 at 20:02

7

you can simply escape & by following a dot. try this:

INSERT INTO STUDENT(name, class_id) VALUES ('Samantha', 'Java_22 &. Oracle_14');

answered Sep 06 '17 at 20:02

Jerry

316
5
11

3

But this will insert "." as well. Any thoughts? – Teju MB Oct 16 '19 at 06:29

score 3 · Answer 5 · answered Mar 12 '15 at 07:02

3

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.

answered Mar 12 '15 at 07:02

Chikku Jacob

2,114
1
18
33

score 1 · Answer 6 · edited Mar 11 '20 at 10:24

1

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.

edited Mar 11 '20 at 10:24

Ankit Bajpai

13,128
4
25
40

answered Aug 03 '12 at 08:13

lakhcoool

21
1

1

Thats's what EternalNoob said, and close to what Craig said, when the question was first asked. – Alex Poole Aug 03 '12 at 22:35
Not sure why this is not the most upvoted answer. – Stamatis Tiniakos Mar 13 '22 at 16:20

score 1 · Answer 7 · answered Apr 03 '17 at 07:02

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 ' || '& '|| ':' || ' 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 !!!

score 0 · Answer 8 · edited Oct 10 '13 at 13:34

0

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)||'');

edited Oct 10 '13 at 13:34

Simon Adcock

3,554
3
25
41

answered Oct 10 '13 at 13:10

Surya

11
2

score 0 · Answer 9 · edited Oct 05 '16 at 16:33

0

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

edited Oct 05 '16 at 16:33

Robert Columbia

6,313
15
32
40

answered Oct 05 '16 at 12:21

Ender Akdoğan

9
1

Luis Cardoza Bird · Answer 10 · 2017-01-13T16:44:04.437

0

Also you can use concat like this :D

Insert into Table Value(CONCAT('JAVA ',CONCAT('& ', 'Oracle'));

edited Jan 13 '17 at 16:44

answered Jan 13 '17 at 15:25

Luis Cardoza Bird

1,265
4
24
43

score 0 · Answer 11 · edited Mar 11 '20 at 10:25

0

strAdd=strAdd.replace("&","'||'&'||'");

edited Mar 11 '20 at 10:25

Ankit Bajpai

13,128
4
25
40

answered Jun 06 '17 at 07:13

Gank

4,507
4
49
45

score -1 · Answer 12 · answered Mar 24 '17 at 19:25

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.

score -4 · Answer 13 · answered Nov 14 '10 at 00:36

-4

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}'

answered Nov 14 '10 at 00:36

Maria Ioannidou

1,544
1
15
36

your first approach does not work. – Rachel Nov 14 '10 at 00:40
It prints bracket too for second approach and `\&` for first approach. – Rachel Nov 14 '10 at 00:42
2

Anonymous user says: *Remark: this is originally instructed in Oracle documentation but not working in client like SQL Developer.* – Pubby Dec 20 '11 at 02:34

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

13 Answers13

Linked

Related