Solved

How to insert single quotes and double quotes into Oracle8i table

Posted on 2003-11-10
7
1,613 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
7 Comments
 
LVL 23

Accepted Solution

by:
seazodiac earned 32 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 31 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 31 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 31 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
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.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

810 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