Home » SQL & PL/SQL » SQL & PL/SQL » single quote problem
single quote problem [message #36969] Tue, 08 January 2002 06:23 Go to next message
Messages: 15
Registered: November 2001
Junior Member

I want to insert a string into a field with varchar2 type. The string comes from user input. Let's say if the user input string like this: O'Reily's Oracle book,then I can insert that string into the table because of the 2 single quotes. I know you can add another single quote after the first single quotes to do that. But how do you change the user input from one single quote to 2 single quotes with your program?

Re: single quote problem [message #36973 is a reply to message #36969] Tue, 08 January 2002 09:31 Go to previous message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
If you keep the value having the single quote in a pl/sql variable then it's no problem. It's only wen you start building up SQL strings containg the single quote and then executing that string that you get a problem. You should have a very good reason for using dynamic SQL like this rather than using variables.

to convert one single quote to two, use replace()

set serveroutput on size 10000
-- we need one here to get a single quote into the variable
v_str varchar2 (20) := 'O''reilly';
DBMS_OUTPUT.PUT_LINE ( 'original single quoted v_str= ' || v_str );
v_str := replace(v_str, '''', '''''');
DBMS_OUTPUT.PUT_LINE ( 'after double quoted v_str= ' || v_str );

original single quoted v_str= O'reilly
after double quoted v_str= O''reilly
Previous Topic: package problem
Next Topic: Outputting leading spaces with serverouput within SQL*PLUS
Goto Forum:

Current Time: Thu Aug 06 02:25:38 CDT 2020