1 of 1 people found this helpful
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.
1 of 1 people found this helpful
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.
Yes, case sensitive! I often request in this fashion to compensate:
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:
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://technet.microsoft.com/en-us/library/ms179859.aspx> (of course, eSQL does NOT use the "" or "^" wildcards...)
Since SQL syntax is NOT case sensative, I was not aware of this at first, however I had tried both ways, without succes. You got me thinking, however and I did locate the issue further up in my script! I had requested that $$myvar to return lowercase, once I eliminated that the Execute SQL function returned correctly! The odd thing is that I was unable to get the any result from using the wildcard (%) on one side or the other of $$myvar. I ran the script in the debugger with the data viewer open and it appears that it simply was not executing....
That's a good idea! In this case There are multiple words in the field that are both capitalized and lowercase, since there is a specific manner in using capitalization in these particular records, I wont have to worry about it. Your approach could be beneficial next time!
I tried using LOWER, however it would not execute either...In the end we are sticking with it being case sensative, since this solution involves specific records that will always use specific upper and lower case values, it's not a problem. 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.
Are you saying that both %P and %p fails ?
> 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.
ExecuteSQL ( "SELECT myField FROM myTable WHERE myField LIKE ?" ; "" ; "" ; $$myVar & "%" )
ExecuteSQL ( "SELECT myField FROM myTable WHERE myField LIKE ?" ; "" ; "" ; "%" & $$myVar )
Fail to evaluatate
%p will only find a p with no trailing characters, e.g. a word that ends with p
> Are you saying that both %P and %p fails ?
> Yes! Both Fail to evaluatate
Message edited by: gdurniak
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.
// 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
"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 ] ; [ "|*|" ; ¶ ] ) ) )