MySQL Forums
Forum List  »  Newbie

Re: Lower case queries
Posted by: markel_mike
Date: May 01, 2005 12:29PM

You probably already know this, but this seems like a poor application design to begin with. If the widgets field is important, why is your application allowing so many disparate and unpredictable ways to populate the field. Your users should be only allowed to use a drop down box where the values are controlled by you, either from a table or hard coded in the value list.

Agree?

The problem is that when you use LIKE syntax you have to quote/delimit (%). When you do that, the characters within the delimiters are taken literally....and thats all it can select...the values that are literally inside the delimiters.

I don't think this problem can be handled in a SQL statement.

Maybe..... (from the manual) using one the following:
/******/
This function is multi-byte safe.
SOUNDEX(str)
Returns a soundex string from str. Two strings that sound almost the same should have identical soundex strings. A standard soundex string is four characters long, but the SOUNDEX() function returns an arbitrarily long string. You can use SUBSTRING() on the result to get a standard soundex string. All non-alphabetic characters are ignored in the given string. All international alphabetic characters outside the A-Z range are treated as vowels.
mysql> SELECT SOUNDEX('Hello');
-> 'H400'
mysql> SELECT SOUNDEX('Quadratically');
-> 'Q36324'

Note: This function implements the original Soundex algorithm, not the more popular enhanced version (also described by D. Knuth). The difference is that original version discards vowels first and then duplicates, whereas the enhanced version discards duplicates first and then vowels.
expr1 SOUNDS LIKE expr2
This is the same as SOUNDEX(expr1) = SOUNDEX(expr2). It is available only in MySQL 4.1 or later.
/*******/

You will probably have to do this in your program code by doing string manipulations on the %widgets% variable for several different combinations of capitalization....append the results of each record set into a temp table, and after you have exhausted all combinations on the %widgets% variable, present the records of the created temp table to the user.

This is more complex than it ought to be, but due to the poor data integrity in the widgets field, you do not have many good choices.

If it were me, I would bite the bullet and cleanse the values in the widgets table and all others, and then put data integrity controls to keep the "widgets" field clean. In the long run, it's your only best option.

Good luck.

Options: ReplyQuote


Subject
Written By
Posted
May 01, 2005 04:31AM
Re: Lower case queries
May 01, 2005 12:29PM
May 01, 2005 12:41PM
May 01, 2005 03:07PM
May 01, 2005 04:19PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.