Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Single Quote ( ' ) problem in SQL
Ajay M. Kakade wrote:
>
> I have a main table A and contains a column Name. For this column Name I have
> a lookup table. In many of the names there is a single quote present
> for example O'Conner. In my application I display a list of Names from the
> lookup table. When the user selects names like O'Conner a dynamic SQL is
> generated which looks like
> Select blah, blah, blah
> From A
> where Name = 'O'Conner';
> This bombs with an error. The biggest consideration is that all my queries
> are generated dynamically using some algorithm which would select rows from
> tables and generate a SQL from the users input. So I do not have much control
> over the query.
> Has somebody got a solution for this Single Quote Problem. I would be
> grateful if somebody would share a solution with me.
>
> Thanks in advance.
>
> Ajay Kakade
If you are creating the dynamic SQL yourself then the solution is rather easy. When the user selects a name, perform a string replace to replace all occurrences of a single quote with two single quotes. If using PL/SQL it would be:
name := REPLACE(name, '''', '''''')
This will replace all single quotes with two single quotes and your statement will now look like:
Select blah, blah, blah From A where Name = 'O''Conner';
This statement will provide the desired results.
Chris Halioris
Tactics, Inc.
hali_at_tacticsus.com
Received on Tue Mar 11 1997 - 00:00:00 CST