Another SQL question: é

This contact picker by J. Young uses the UPPER function to avoid capitalisation-related issues in the search. In Canadian French, UPPER doesn't remove the accent (we accentuate caps) so é becomes É. Sometimes people will enter their names with or without the accent, depending of the person who logs the info, or the input system limitations etc. So sometimes my name is Cécile and sometimes I figure as Cecile in certain databases.

SQL isn't forgiving; if you put the accent in the search it won't find Cecile and if you don't put it, it won't find Cécile.

SQL looks for an exact match, thus you convert both parameters to UPPERCASE for the test. If one of the parameters may contain accented characters, you could you use Substitute to remove the accents by replacing ”É” by “E”, ”Ë” by ”E”, etc. once the parameter is uppercased. That’s about the only way I guess you can prepare the parameter for comparison.

LIKE should not be used if you need "exact match". If OTOH (on the other hand), you are trying to get a "proximity search" (proximité) or "fuzzy search", then you may have the values that might match (to lookup). For existing data (with accents and diacritic marks), that means, you may need to create a calculation (auto-enter might be better), that substitutes out the characters (including upper and lower chase) to 'plain' - this is called normalizing strings in many other apps. Then your search would be OR:

If you are using the "LIKE" because it is not just the beginning, end or 'exact', then go ahead, but your searches may be slower. Also you may use the "UPPER()" - SQL function & "Upper()" - FM function, but that can be slower as well.

I understand what you are trying to achieve (with the check for duplicates or just find a near-match). Pre-eSQL, this was done with some fancy footwork (calculations) to make the "patterns" that might match in a return-delimited list.

Beverly =>

B

BE

BEV

BEVE

BEVER

BEVERL

BEVERLY

I would not go much beyond a "full match" (on the word(s)) and up to 5 characters. So the calc would be something like:

= Upper(

Left(name;1) & Char(13) &

Left(name;2) & Char(13) &

Left(name;3) & Char(13) &

Left(name;4) & Char(13) &

Left(name;5) & Char(13) &

name )

And actually, a relationship between a search field (global storage, perhaps) and this "auto-enter" calc would yield the "matched list". You can do the same with multiple words, but it gets more complex. I'd used two fields:

You would also include any fields set to "plain" as part of this "field-to-search". Since it's a "multi-line key" and not really a good fit for ExecuteSQL() as-is, I've merely provided for alternative method to doing the "fuzzy search".

I searched for possible other method(s):

Finding similar data (which refers to the Levenshtein and Soundex custom functions to help with these kind of searches)

Works EXACTLY like Excel's VLOOKUP function: Searches in a specific field for the first (close or exact) match, and returns the value from a different field in the same record. -- This function can find nearby matches or exact matches, is NOT case sensitive, returns only one value, and will return #N/A if no match is found. Can pull data from any table (even from non-related tables).

I prefer the Char(13) - which is the carriage return - rather than the pilcrow (¶). Both work, but sometimes this may need to be Char(13) & Char(10) or Char(10) - CRLF & LF, respectively. The end-of-line can be diverse, so I rarely rely on the Pilcrow to get what I desire these days.

You asked for a little more than the standard, so I put out there what others have done. ExecuteSQL() may not fit the need.

I am not familiar with SQL. I use it when it is suggested to me or if used in a solution I copy. Hopefully one day I will be savvy enough to be able to assess various strategies on the fly like you guys do and choose for myself rather than mimicking others. (Learn the rules perfectly before you break them creatively...)

At CQDF conference in Montreal last March, I attended a presentation on JSON and it stopped sounding like an horror movie character to me but I have yet to study it in more depth and learn to recognize "flags" to alert me that "oh, here is a place where a JSON would be a good strategy".

I suppose your question (and some of the answers), points to the "should I use eSQL or should I use other functionality"? That's why I answered as I did. There are ways that work without the SQL call that may give you what you need. (Michael) comment pointing out that FileMaker is not case-sensitive, & not 'exact' (unless you tell it to be), so a standard FIND is going to give you a good deal of your 'fuzzy search" without so much fuss!

The use of JSON may or may not help for your needs.

This may be a use-case for the Master-Detail (current table portal), where you simply SEARCH and the portal shows you the 'matches'. I will also teach most of my clients how to use the "wildcards"/symbols when doing finds and that can get a bunch of the 'fuzzier' matches, too!