Re: Single quote in search string.

I wrote a script that would generate another SQL script to analyze indexes automatically.

I got headaches just from having to track those double quotes, triple quotes, etc.

There is a little blurb about this in the O'Reilly Oracle PL/SQL Programming book, by Steven Feuerstein.

Regards,

Pat.

Robert Wagner wrote:

> If I want do search for a single quote, is the only option to double the
> single quote?
>
> In other words is constructing the SQL statement like this the only way to
> handle the problem?:
> strTarget = "'I can''t go on!"
> strSQL = "SELECT * FROM MY_TABLE WHERE MYFIELD = ' " & strTarget & "'"
> (I'm using Access to construct strSQL, and the last is
> double-single-double quotes.)
>
> (I'm using doublequotes to delineate the string, since I'm working in
> Access, using ADO against an Oracle backend.)
>
> It could get messy if there are TWO or MORE single quotes in the search
> item.
>
> It'd be nice if we could have the option of defining a '' (two single
> quotes) as the delimiter, similarly to the way it's done in Access (where
> double quotes are used as the delimiter. There, the string would look like:
> strTarget = "'I can't go on!"
> strSQL = "SELECT * FROM MY_TABLE WHERE MYFIELD = """ & strTarget & """"
> (In the last line, all quotes are double-quotes.)
> This last looks worse, but it's actually better, since I don't have to
> 'customize' the target string.
Received on Tue May 02 2000 - 00:00:00 CDT