Yes, Morgan. That is the way it works in the SQL dbs!
-- sent from my iPhone4 --
So are you saying that using the UPPER() function is the way you would do this with a real SQL database? If so, that makes perfect sense, as that's from whence I choose this appraoch.
But my quetion is specific to the ExecuteSQL() function in FileMaker. Are you telling me that using UPPER() is the proper (and best-performing) method to use when searching a FileMaker database using ExecuteSQL?
What I'm concerned with is the possibility that if I am searching 50,000 Contacts (for example), I wouldn't want to use some method that caused FileMaker to abandon the use of existing indexes so that each Contacts record had to be retrieved and the search field upshifted before performing the comparison to the search criteria. Does this make sense?
Thanks for any clarification ...
If I need case-sensitive search in SQL, it depends on the COLLATION of the column (field). If it's set to "Latin1_General_CS_AS", then it is case-sensitive. But by default it may or may not be set that way.
But if I'm not sure, I might use LOWER() function (or UPPER, but I seem to prefer lower) to make a SQL search.
I don't know "under-the-hood" for ExecutesSQL function in FileMaker, so I can't tell you why your search might be case-senstive even though your field is NOT set to 'Unicode' indexing. Just out of curiosity, what is it set to, Morgan?
Also, I might use an "if". Search for "LIKE '%morg%'" first. If you are sure there should be records, but you return none, then do the UPPER/LOWER search next.
After the holiday, let's see if the FMI folks will chime in...
Thanks for your thoughts on this … I am looking for the “under-the-hood” details, so I hope the FileMaker, Inc. folks can shed some light.
The field in question has “English” specified for the indexing language. Thus I was surprised that the searching performed by the ExecuteSQL function was case sensitive. I always figured this new function would use the same indices and retrieval logic as a native Find, but I guess not (unless, of course, I’m just making a dumb mistake somewhere … always a possibility).
Peace, love & brown rice,
FileMaker + Web: Design, Develop & Deploy
Certifications: FileMaker 9, 10 & 11
<http://www.onepartharmony.com/> One Part Harmony
Austin, Texas • USA
Not an expert by any stretch but have been studying pretty heavily as of late given the magnitude of what is possible with SQL. From my experience, there is a whole lot of case sensitivity going on. I always use lower on both the statement as well as the argument syntax. I can be staring at a statement that is giving me a return just as I was hoping to achieve, one in which my getting there called for the use of the double lower; lower in the statement and the argument- then to test the query's fortitude, begin pulling things away to see if a lighter query will fly but as soon as i pull either lower out...the return goes away. From what I understand there is an "I like" which is case insensitive but filemaker doesn't support it's use. I'm assuming u have peeped out seedcode's SQL explorer (free). As well, skeleton key has a YouTube video dedicated to the topic as part of their filemaker academy. It is pretty good so far. It's about 50mins and I only got to 18. Min 16 or so is where the I like point came from. Check it out.
If u need more, I have another set of resources I can send u - email me. Pretty sure my email addy is in
Y profile. At 5a , too lazy to iPhone scroll over to my profile ;)