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

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

Outcomes