ExecuteSQL: How to make it case-insentitive?

Discussion created by datastride on May 27, 2012
Latest reply on May 29, 2012 by reelsteve



The new ExecuteSQL function is an amazing tool, but I've run into one issue for which I could use some guidance.


Take the following SELECT statement as an example:


SELECT First_Name, Last_Name

FROM Contacts

WHERE First_Name LIKE 'morg%'


I was expecting that this would find my Contact record, as my first name is "Morgan". (note capitalization)


But only by changing the SELECT statement as follows does this work:


SELECT First_Name, Last_Name

FROM Contacts

WHERE UPPER( First_Name ) LIKE UPPER( 'morg%' )


I'm writing a script to process keystrokes and display matching Contact names, so I want to use the most efficient form of the SELECT statement possible. In my real script, the "morg" is actually a variable based on characters typed by the user. And I want the users to be able to type a name (or part of a name) without being concerned with capitalization. Using the UPPER function produces the deired results, but is it the best-performing solution?


FileMaker's native FIND is case insensitive unless I specify "Unicode" as the language to use for indexing. I'm wondering if I can somehow force FileMaker to use the same native, case-insensitive logic for a SELECT as for the native FIND (so I get the best performance). I don't know if using the UPPER() function adds extra overhead or if it is evaluated by FileMaker in such a way as to enable use of the native (and very efficient) case-insensitive FIND logic.


Thanks in advance for any light you might be able to shed on this issue ...