Why don't you use bind variables? They are better for performace, and
you will not have to deal with single quotes...

HTH,
Patricia.

-----Original Message-----
From: sean feifer [mailto:[Email Address Removed] Quarta-feira, 31 de Julho de 2002 10:27
To: 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 serves as 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!



Richard Quintin <[Email Address Removed] wrote:


The function you are looking for is translate.
select translate('VIP''s', '''', '"') from dual;


Richard Quintin
VTLS, Inc.
http://www.vtls.com/ <http://www.vtls.com/>

Subject: Re: Single Quote in Select Search




Thanks for all who responded. But my problem is how to make the sql
statement to convert ' to ''.


The Search Form is like : SEARCH __________. People would put in Keyword
like VIP or VIP's If it is VIP, I have no problem with SQL behind the form,



" select * from table where title='Keyword'.


If it is VIP's, it gives error cause it doesnt recognize " ' ". I need to
modify the sql statement to convert ' to ''. I am trying to use decode to
convert, but I have not found the right way yet. I hope there might be some
kind of function to do if ', then ''.



Thanks!






_____

Do You Yahoo!?
Yahoo! Health <http://health.yahoo.com/> - Feel better, live better

Oracle LazyDBA home page