SQL LIKE EXPRESSION NOT WORKING
Having an issue getting FMP to "like" my SQL LIKE wildcard expression.
The below expression does not find the customer record from the search field with "ON" in it (as in "JONES").
ExecuteSQL ( "Select count(*) from Customer where UPPER(CUS_LastName) LIKE ?"; "" ; "" ; Upper("%Customer::CUS_SEARCH%"))
Adding the field name as in the example above seems to prevent the wildcard expression from working. The script debugger shows me that the value of the field in CUS_SEARCH is "ON".
Yet, in the FMP script debugger, the following works:
ExecuteSQL ( "Select count(*) from Customer where UPPER(CUS_LastName) LIKE ?"; "" ; "" ; %ON%"))
So, what's with just adding the field name?
Using a JDBC connection, I can externally do the LIKE expression fine and even in an external program against the FMP database, but I still am not sure how well supported LIKE is within FMP.
What's up here?
Thanks in advance.