ExecuteSQL and Wildcard (%) with LIKE operator ISSUE

Hi Amy,

Just as a reminder:  ExecuteSql is case sensitive.

This would explain some of the results that you are seeing, though, unfortunately, not all of the results shown above.

I would have still expected, for instance,  'p' to match on the lower case  'p'  in 'Purple'.

On the other hand, case-sensitivity would explain, for instance, why 'un' does not match 'Unicorn', but does match 'Yellow Bunny'.

Apologies if I'm just stating the obvious to you.

Very best,

-steve

HI Amy,

One possible issue is that SQL is case sensitive.  in my case I was looking for names in so I created my the $srch variable using the proper() function.  This capitalized the first letter for me.

I just did this for a name look up and used $$myVar & "%".  This worked well for me.

HTH

Bruce

Yes, case sensitive! I often request in this fashion to compensate:

     SELECT myfield

     ...

     WHERE LOWER(myfield) LIKE ?

     ....

          "%" & Lower($myVar) & "%"

Note that the SQL has a LOWER (and UPPER) function that can be used with the FileMaker function on your parameter term. This makes them 'equal' for searching. Yes it can take longer. The result has the actual field, so the Lower() is only applied for the match.

also check for these gotchas:

*     the LIKE '%term%' should be in single quotes. I'm not sure if your method is making this occur correctly. Probably as you are passing a term composed of concatenated wildcards and your variable, this IS getting formatted correctly by FileMaker

•      This does not have the additional wildcard for "U" and the space would be literal in the match:

"urple nicorn"

 

You can also use the "_" wildcard for a single character:

     LIKE '_urple _nicorn'

This should take any single character regardless of case. Searching for "_" in the field, of course adds it's own complexity!

see if these links help:

<http://www.w3schools.com/sql/sql_wildcards.asp>

<http://www.tutorialspoint.com/sql/sql-wildcards.htm>

<http://technet.microsoft.com/en-us/library/ms179859.aspx> (of course, eSQL does NOT use the "[]" or "^" wildcards...)

HTH,

Beverly

Are you saying that both  %P and %p  fails ? 

greg

> What I don't understand is why when I used the wildcards (%, _ ), either infront or behind the search term, the function failed to evaluate all together.

Correction:

%p will only find a p with no trailing characters,  e.g. a word that ends with p

greg

> Are you saying that both  %P and %p  fails ?

> Yes!  Both Fail to evaluatate

Message edited by: gdurniak

HI,

Below is an abstracted query out of Seedcode's SQL explorer with two differences that may be of help.  This was being testing in the data viewier do I set the search criteria at the top with $ck = lower("P") & "%".  The second second difference is that I added the SQL LOWER function in the critteria line. 

"WHERE LOWER(" & aClientName & ") LIKE ? " ;

Notice that the WHERE LOWER(  and ) LIKE ?  section are both in quotations so that they will be interpreted correctly and change the value of the search field to lower case.

HTH

Bruce

// Built by SQLExplorer.  Compliments of SeedCode… Cheers!

Let ( [

$ck = lower("P") & "%";

// Define Carriage Return Substitution Character

ReturnSub = "\n" ;

// Enable the second line here if you want the header in your results

header = "";

//header = "a.ID";

// Define Table variables

aCLIENTS = Quote ( GetValue ( Substitute ( GetFieldName ( CLIENTS::ID ) ; "::" ; ¶ ) ; 1 ) ) & " a" ;

// Define Field Variables

aID = "a." & Quote ( GetValue ( Substitute ( GetFieldName ( CLIENTS::ID ) ; "::" ; ¶ ) ; 2 ) ) ;

aClientName = "a." & Quote ( GetValue ( Substitute ( GetFieldName ( CLIENTS::ClientName ) ; "::" ; ¶ ) ; 2 ) ) ;

// Build SQL Query

q =

"SELECT " & aID & "

FROM " & aCLIENTS & "

WHERE LOWER(" & aClientName & ") LIKE ? " ;

// Run SQL Query

result = ExecuteSQL ( q ; Char ( 9 ) ; "|*|" ;   $ck ) ] ; 

// Clean up carriage returns

List ( header ; Substitute ( result ; [ ¶ ; ReturnSub ] ; [ "|*|" ; ¶ ]  ) )  )