| single quote problem [message #36969] |
Tue, 08 January 2002 06:23  |
Kane
Messages: 15
Registered: November 2001
|
Junior Member |
|
|
Guys:
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?
Thanks
|
|
|
|
| Re: single quote problem [message #36973 is a reply to message #36969] |
Tue, 08 January 2002 09:31  |
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
declare
-- we need one here to get a single quote into the variable
v_str varchar2 (20) := 'O''reilly';
begin
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 );
end;
/
original single quoted v_str= O'reilly
after double quoted v_str= O''reilly
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
