Solved

How to insert single quotes and double quotes into Oracle8i table

Posted on 2003-11-10
7
1,611 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
UNIX SCP 5 45
null value 14 57
Migrate Oracle Database from ASM to Non-ASM on a Windows server. 1 11
Best RAID for a BDD Oracle 4 12
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

763 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

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now