Please don't do any of these things. Use bind variables. You will not have to worry about ' or any other special character. This will also improve your performance. If you send in every query with a different hard-coded string, you will never reuse any of the queries (unless someone searches on exactly the same string multiple times). Bind variables are handled a little differently in each host language, and I don't know java well enough to give an example, but in perl DBI/DBD, you would do something like

my $dbh = DBI->connect(); # with appropriate connection info
my $keyword = &getKeywordSomehow();

#there is more than one way to do this, of course. See the docs.
my $searchQuery = $dbh->prepare("
select * from table where title=?
");

$searchQuery->execute($keyword);

# extract the data from the cursor handle $searchQuery
# in one of the many ways available

...


-----Original Message-----
From: Richard Quintin [mailto:[Email Address Removed] Wednesday, July 31, 2002 9:36 AM
To: LazyDBA.com Discussion
Subject: RE: Single Quote in Select Search


select * from table where title=translate('KEYWORD', '''', '"')

Richard Quintin
VTLS, Inc.
http://www.vtls.com/
-----Original Message-----
From: sean feifer [mailto:[Email Address Removed] Wednesday, July 31, 2002 9:24 AM
To: Richard Quintin; LazyDBA.com Discussion
Subject: RE: Single Quote in Select Search


That is a good point. But how can I incorporate it into my statement so that it catches ' and convert to " when receiving a value containing '. The statement is "select * from table where title='KEYWORD' " Pls note the KEYWORD servers a variable. It does not always contain ' . I want the SQL to catch a value either with or without '. That means I need to have a function to attach to the SQL so that it can convert ' to '' if it sees one.
Thanks!


LEGAL NOTICE:
Unless expressly stated otherwise, this message is confidential and may be privileged. It is intended for the addressee(s) only. Access to this e-mail by anyone else is unauthorized. If you are not an addressee, any disclosure or copying of the contents or any action taken (or not taken) in reliance on it is unauthorized and may be unlawful. If you are not an addressee, please inform the sender immediately.
Oracle LazyDBA home page