Re: How to use a ' in dynamic SQL statement

Using two quote characters together like this '' will cause it to be interpreted as a quote inside the string, rather than as a string terminator.

So your SELECT statement would look like this:

sqlString := 'SELECT * FROM table WHERE column = ''Y'' ...';

Of course, this can also get confusing if you have too many quotes beside each other and can't figure out what starts where.

If you know that your data will not contain the double-quote character ( " ), another approach is to write a function that will replace double-quotes with single quotes, so your string would look like this:

sqlString := replacequote('SELECT * FROM table WHERE column = "Y" ...');

  morrisj_at_cableregina.com (Jason Morris) wrote:
> Hi all, hope there is an easier way to do this.
>
> I'm using dynamic SQL in a stored procedure, and would like to do
something
> like this:
>
> sqlString := 'SELECT * FROM table WHERE column = 'Y' ... ';
>
> Now the "column='Y'" causes problems, because it thinks they're string
> terminators. Is there an escape character or something similar? In
SQL Server
> it was just " column=''Y'' ".
>
> Right now I'm stuck doing:
> column = ' || chr(39) || 'Y' || chr(39) || ' .... '
> but this strikes me as ugly, and there should be a better way (I'd
like to
> think).
>
> Comments? (And yes, I must use dynamic SQL, it's not an option)
>
> Thanks for any help
> Jason Morris
> morrisj_at_cableregina.com
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Tue Feb 08 2000 - 10:44:42 CST