Dale,
As far as I can tell, that SQL statement is correct. I’m using SQL Server, but it should be universal. The fact there are two single-quotes in place of the one in the name is the effect of encoding=sql. Doing a few tests might help. Try executing these statements and seeing what you get: SELECT upper(‘prud’’homme’) SELECT upper(lname) from scientists where lname like ‘%homme%’ Also, verify that you should be finding the record – that it’s ORID is what you expect and that there is a joined record in the IPID_ORID_SCID table. Robert From: Dale Graham [mailto:[email protected]] Sent: Tuesday, September 20, 2011 10:25 AM To: [email protected] Subject: Witango-Talk: Searching names containing apostrophes If I search on something like this Prud'homme no results are returned, despite the fact that this person (Robert Prud'homme) exists in the database (Note that I am using <@upper to keep this case insensitive). I created this by taking the lname variable and making it upper case and searching with the custom column. But looking at the sql statement, I can see why it failed. SELECT DISTINCT S1.SCID FROM SCIENTISTS S1,IPID_ORID_SCID I2 WHERE (upper(S1.lname)='PRUD''HOMME' AND upper(S1.fname)='ROBERT' AND I2.ORID=?) AND ((S1.SCID=I2.SCID)) However, I have tried searching using <@var request$lname encoding=sql> but that didn't fix the issue. And I cannot figure out how to do a replace of an apostrophe (e.g., single quote) with <@sq>. I also tried getting the data to be searched (being parsed from XML files) and setting the variable to encoding=sql initially as well. No joy. Ideas? Fixes? _____ To unsubscribe from this list, please send an email to [email protected] with "unsubscribe witango-talk" in the body. ---------------------------------------- To unsubscribe from this list, please send an email to [email protected] with "unsubscribe witango-talk" in the body.
