?
Solved

How to insert single quotes and double quotes into Oracle8i table

Posted on 2003-11-10
7
Medium Priority
?
1,617 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 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

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

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 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Suggested Courses

777 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