Link to home
Start Free TrialLog in
Avatar of formadmirer
formadmirerFlag for United States of America

asked on

VFP MySQL Select LIKE

Hello experts!

In VFP 9 I'm trying to do a 'LIKE' select from mysql but am having trouble with the syntax.
The current code looks like this:

lcQuery="SELECT product_id FROM " + lcTableName + ;
" WHERE name LIKE ?lcLookup AND product_id > ?lnProdID " + ;
"ORDER BY product_id ASC LIMIT 1"

Open in new window


Column 'name' contains a list of foods.

This search works fine if 'name' begins with the searched criteria - i.e. my search for 'ice' will return 'ice cream'.

But I would like my search for 'ice' to also return 'real ice cream'.

I've tried everything and every combination I can think of - including % before and after the ?lcLookup as well as using INSTR - all I get are syntax errors with no results returned.

Tried:
WHERE name LIKE %?lcLookup%
WHERE name LIKE ?%lcLookup%
WHERE name LIKE `%?lcLookup%`
WHERE name LIKE `{%?lcLookup}%`
WHERE INSTR(`name`, `?lcLookup`)
WHERE INSTR(`name`, `?lcLookup`) > 0
WHERE INSTR(`name`, `{?lcLookup}`) > 0

Thanks!
SOLUTION
Avatar of Olaf Doschke
Olaf Doschke
Flag of Germany image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of formadmirer

ASKER

I found an additional problem, I couldn't understand why suddenly it wasn't working as I've been using this search for a number of years without problem.

Recently though i started working on a new project and the tables here are collated utf-8 bin. I'm guessing the search is now case sensitive and that was also causing no matches to be returned.

As for the suggestions above, is one 'quicker' than the other?
'name' is not indexed and I cannot index it.

Also, how would I write INST() taking into account that I will UPPER the search criteria?

lcLookup = UPPER(lcLookup)
WHERE INSTR(`UPPER(name)`, ?lcLookup) > 0

???

Thanks!
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I wound up using % and with the adjustment for case sensitivity it's working now - thanks!
Instead of using UPPER you can make use of COLLATE and change to a _ci (case insensitive) collation in your SQL:

MySQL documentation says: http://dev.mysql.com/doc/refman/5.0/en/case-sensitivity.html

This means what also would work with LIKE is

... WHERE `name` COLLATE utf8_general_ci LIKE ?lcLookup

The choice of utf8_bin is not the best choice in the first place. It makes comparisons and sorting case sensitive, you normally want as _ci collation.

Bye, Olaf.