Log in to create and rate content, and to follow, bookmark, and share content with other members. Not a member? Join Now!

AnsweredAssumed Answered

ExecuteSQL and Wildcard (%) with LIKE operator ISSUE

Question asked by amy on Jan 27, 2014
Latest reply on Feb 6, 2014 by BruceHerbach

Like • Show 0 Likes0
Comment • 10

Hi Everyone,

I am using the ExecuteSQL funtion in FileMaker 13 to set a variable using the LIKE operator with wildcards (%) on either side of another variable like this;

ExecuteSQL ( "SELECT myField FROM myTable WHERE myField LIKE ?" ; "" ; "" ; "%" & $$myVar & "%" )

The problem I'm having is that it only returns records from the second letter of each word. For example, let's say I want to search for Purple Unicorn - and my records are as follows;

Purple Polar Bear

Purple Kangaroo

Pink Panther

Blue Mule

White Unicorn

Purple Unicorn

Yellow Bunny

If I "p" I get nothing, but if I type "u" - I will get

Purple Polar Bear

Purple Kangaroo

White Unicorn

Purple Unicorn

Yellow Bunny

but if I type "un" for unicorn I get only Yellow Bunny

I can type "urple" and I will get -

Purple Polar Bear

Purple Kangaroo

Purple Unicorn

I can type "nicorn" and I will get

White Unicorn

Purple Unicorn

But I get nothing if I type "urple nicorn"

Further if I change the wildcard to be $$myVar & "%" or "%" & $$myVar NOTHING is returned regardless of what is typed.

Does anyone know what is going on here. Unless the LIKE operator can be used as intended, I really don't see the purpose!

Thanks,

Amy

No one else has this question

Outcomes

10 Replies

steve_ssh Jan 27, 2014 9:11 PM
Mark Correct
Correct Answer
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
1 person found this helpful
Like • Show 0 Likes0
Actions
BruceHerbach Jan 27, 2014 9:20 PM
Mark Correct
Correct Answer
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
1 person found this helpful
Like • Show 0 Likes0
Actions
beverly Jan 28, 2014 8:46 AM
Mark Correct
Correct Answer
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
Like • Show 3 Likes3
Actions
amy @ steve_ssh on Jan 31, 2014 11:26 AM
Mark Correct
Correct Answer
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....
Like • Show 0 Likes0
Actions
amy @ BruceHerbach on Jan 31, 2014 11:29 AM
Mark Correct
Correct Answer
Hi Bruce,

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!

thanks!
Like • Show 0 Likes0
Actions
amy @ beverly on Feb 2, 2014 11:39 AM
Mark Correct
Correct Answer
Hi Beverly,

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.

Thanks!
Amy
Like • Show 0 Likes0
Actions
gdurniak @ amy on Feb 3, 2014 4:35 AM
Mark Correct
Correct Answer
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.
Like • Show 0 Likes0
Actions
amy @ gdurniak on Feb 4, 2014 6:37 PM
Mark Correct
Correct Answer
Yes! Both;

ExecuteSQL ( "SELECT myField FROM myTable WHERE myField LIKE ?" ; "" ; "" ; $$myVar & "%" )

and

ExecuteSQL ( "SELECT myField FROM myTable WHERE myField LIKE ?" ; "" ; "" ; "%" & $$myVar )

Fail to evaluatate
Like • Show 0 Likes0
Actions
gdurniak @ amy on Feb 5, 2014 5:15 AM
Mark Correct
Correct Answer
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
Like • Show 0 Likes0
Actions
BruceHerbach @ gdurniak on Feb 6, 2014 11:52 AM
Mark Correct
Correct Answer
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 ] ; [ "|*|" ; ¶ ] ) ) )
Like • Show 0 Likes0
Actions

ExecuteSQL and Wildcard (%) with LIKE operator ISSUE

Outcomes

Related Content

Recommended Content