Paul, I would intercept their search string, and "double" each single-quote/apostrophe before doing the search. in SQL, you "escape" a single-quote by repeating it. Don't overwrite the value for the variable that the user sees, or it will get messy.
SET VAR vUserSearchFor TEXT SET VAR vSQLSearchFor TEXT = (srpl(.vUserSearchFor,'''',''''''',0)) SELECT * FROM searchTable WHERE SearchColumn CONTAINS .vSQLSearchFor The second parameter for SRPL is 4 single-quotes in a row: An open quote, a single quote preceded by the "escaping" single-quote, and then the close quote. The third parameter is 6 single-quotes in a row: Open, escape quote, literal quote, escape quote, literal quote, and close quote. So that SRPL says find every instance of a single-quote, and replace it with two single-quotes. Then the query will search correctly. Bill On Tue, Feb 10, 2009 at 9:01 AM, Paul Buckley <[email protected]>wrote: > I'd like some input on how others handle doing text string searches for > words with apostrophes (i.e. Johnson's). I'm working on a search form and > my default quote setting is a single apostrophe (') and therefore they can't > search for words containing a single apostrophe. I'm thinking of > temporarily setting quotes to ("), doing the search and then setting it > back. Will this mess up other people on different workstations that may be > searching at the same time? Aren't those settings stored locally and not > globally? > > > > Thanks in advance for your input. > > Paul Buckley > > >
