?
Solved

How to insert single quotes and double quotes into Oracle8i table

Posted on 2003-11-10
7
Medium Priority
?
1,619 Views
Last Modified: 2012-06-22
Dear All,

Does anyone know how to insert " (double quotes)  and ' (single quotes ) into oracle8i table.

For example i 'v got a table :
CREATE TABLE "GCC"."SGCCSUSR"("ENCRYPTED" VARCHAR2(132) NOT NULL);

..the value i need to insert is

18571!"#$% !"AEOLR% !"Dhrou(Hlvxz'vcpi%&'(#$%&'("#$%&'("#$%&'(Dhrou(Ndwy&uioh$%&'(cbdv$% !"1214541015 !"DLLLWAB"#$% !" !" !" !"/


How do i do this?

Regards
Dejan
0
Comment
Question by:dejanj59
5 Comments
 
LVL 23

Accepted Solution

by:
seazodiac earned 128 total points
ID: 9720271
if the field you are going to insert is a VARCHAR2 type,

in the value, if you have a single quote, you should double it , for example

the insert value is :

sdf'd       -------> sdf''d

if the value contains double quote, you leave as is,  and SQL engine can differentiate it since you will use single quote to enclose the string value.

so you should be able to succeed to insert one like this:

insert into  "GCC"."SGCCSUSR" values('sdfsd''dcd"sdfsd"sdf');

hope this helps
0
 
LVL 12

Assisted Solution

by:catchmeifuwant
catchmeifuwant earned 124 total points
ID: 9720275
You need to use an escape character (') to enter the data :

a)insert into temp
values('test''');

This will insert it the value -> test'

(or)

insert into temp
values('test'||chr(39);


b)To insert a double quote

insert into temp
values('test'||chr(34));
0
 
LVL 2

Assisted Solution

by:Sairam_S
Sairam_S earned 124 total points
ID: 9720334
All values into the varchar field are to be entered within single quotes. Hence there would be no problem of embedding any doube quotes in to the varchar field.

In case of field values with single quotes, all the single quotes must be repeated twice, i.e for example TEST's must be written as TEST''s.

You can use the String function REPLACE() to do the trick.

Use
Replace(String, '<search>', '<replace>')

Replace(fieldValue, '''', '''''') to get all the single occurences of single quotes to be replaced with 2 occurences of single quotes.

where,
fieldValue = the value that needs to be inserted

parameter2 = '''' (4 single quotes) . You need to enclose the second parameter within single quotes. For example 'A'. As we need to change single quote, you have to enter the single quote twice as the character to be searched for. hence 2 quotes for enclosing the search string and 2 for the search character.

parameter3 = '''''' (6 single quotes) - same as above

I hope this would solve your problem.

0
 

Assisted Solution

by:Anvy
Anvy earned 124 total points
ID: 9859595
I was battling with this as well. There's actually two issues I faced when it comes to the single quotation mark:

1. Using a Select Statement
2. Using an Insert/Update Statement

When using the Select Statement, In VB, I used the Replace function:
Replace(txtTitle, Chr$(39), "''")
Chr$(39) = ASCII Value of a single quote and the replacement string is
double-quote, single-quote, single-quote, double-quote.

When using Insert/Update do the following:
"" & txtTitle & "" - Basically all you are doing is concatenating three strings
First = Empty String
Second = String Literal
Third = Empty String

When I tried replacing a single quote with 2 single quotes, I wound up with 2 single quotes in my literal string, which isn't what you need.

Hopes this helps.
-Anvy
0
 

Author Comment

by:dejanj59
ID: 9880557
THanks guys,

Much appreciated. Oracle needs extra ' at the end of every ' plus i need to do another trick
with set define x where x is literal not in my character string so i pick ^

1) Insert ' at the end of every ' and run this in SQL*Plus to create a new record

set define '^';
INSERT
    INTO GCC.SGCCSUSR
    (   ENCRYPTED )
    VALUES  
('18571!"#$% !"AEOLR% !"Dhrou(Hlvxz''vcpi%&''(#$%&''("#$%&''("#$%&''(Dhrou(Ndwy&uioh$%&''(cbdv$% !"1214541015 !"DLLLWAB"#$% !" !" !" !"/');
commit;

Dejan
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Suggested Courses
Course of the Month12 days, 17 hours left to enroll

579 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question