Solved

How to insert single quotes and double quotes into Oracle8i table

Posted on 2003-11-10
7
1,615 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Help on model clause 5 49
Oracle encryption 12 61
pivot rows to columns 1 46
Fill Date time Field 12 33
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.  …
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows how to recover a database from a user managed backup
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

730 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