apostrophe mark or single quote

[crsdev]

Hello Friends:
what do I do in this situation:

for a where clause

select *
from XXXX x
where x.name= ' john's '

how can i say that it's a apostrophe mark and not a single quotes, so that i don't get an error message.

Thanks.

 

[stefanhei]

Try it this way:

select *
from XXXX x
where x.name= ' john''s '

or better use bind variables.

 

[crsdev]

thanks stefanhei,
can you please elaborate a little on blind variables.
I am using name as a variable and most of them don't have apostrophe, only a couble

 

[SantaMufasa]

CRSDev,

Unfortunately, with all the "single-quote" work-arounds of which I am aware (including using bind variables), the "cure" is as bad as (if not worse than) the "disease". If you have "only a couple" of cases where you are comparing a value that contains a single quote, and you are doing the comparison with manually entered literals (as it appears that you are), then the method that Stefan proposed is generally the least cumbersome.

If the problem surrounds end-users having to enter the value, then we can pursue other avenues, including bind variables.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[crsdev]

Thanks Mufasa,
yes the problem does surround end-user having to enter the value.
what other avenues are there..

 

[SantaMufasa]

What software does the end-user use to enter the values (e.g., SQL*Plus, third-party GUI, scripts, et cetera)?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[crsdev]

Mufasa, They use Business Objects. they basically select from a drop down list.

 

[SantaMufasa]

If they are using Business Objects, then how are they creating code that looks like your example:

select *
from XXXX x
where x.name= ' john''s '

Specifically, what item from a drop down list would look like, " ' john''s ' "?

I'm not trying to be dense or difficult...I'm just wanting to clearly understand your context so that I can suggest a solution that will best meet your need.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[crsdev]

this is the code i use

select id
from some_table a
where a.name='{?NAME}'

the name part is picked by the user from a drop down list.
THanks