Re: Single Quote ( ' ) problem in SQL

Jeff Bangle wrote:
>
> 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 using SQL to generate other SQL statements, you may need to
> use four single quotes: ''''
>
> I don't remeber the logic behind why this is needed, or exactly when,
> but it does work.

In the logic that's generating the dynamic SQL you need to replace all occurrences of ' with ''. So O'Connor becomes O''Connor and your SQL becomes:

         Select blah, blah, blah
         From A
         where Name = 'O''Conner';

Which should work.

         James H. Yoshii
 IntelliTech Business Solutions

        yoshii_at_planet.net