Single Quotes & Query of Query

Well that was fun. Since I'm working in Ireland at the moment there's about a gazillion people with single quotes in their names.

"Query of Query" really doesn't cope very well with that.

CF automagically escapes them with 2 single quotes ('') and then any where clause doesn't find them. Not very useful.

If you use preservesinglequotes, the query is syntactically incorrect. So you get an error. Also, not very useful.

However, if you replace each single quote with 2 single quotes you end up with a param that looks like this 'O''''Reilly'. Somewhat bizarrely, that works a treat.

So, if you want to search for O'Reilly and his mates (from parameter searchValue) using Query of Query, you need to do this: -

#replace(ucase(searchValue),"'","''","all")#

That's rather horrible to read, isn't it. So to make it readable (by adding a bunch of spaces): -

#replace(
ucase(searchValue),
" ' ",
" ' ' ",
"all"
)#


But obviously, you don't want the spaces there in reality.

So there you go. Query of Query is completely mental.
Dave