Thanks Bill,
I'll give this a try. Paul From: [email protected] [mailto:[email protected]] On Behalf Of Bill Downall Sent: Tuesday, February 10, 2009 9:11 AM To: RBASE-L Mailing List Subject: [RBASE-L] - Re: Handling Apostrophes in Text String Searches 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
