MySQL searches

Anon

I would like to know how you can search for partial and full words. For instance i have a field called first name in my mySQL database. If i search for Jarret i would like to show Jarrit and Jarred as search results. I was doing a where clause where fname = frist_name. Is thier something that will search part of it also.

Thanks.

Ira

terje-s

For the example you mentioned, I would have used what's called "soundex". Soundex was developed a while back by god-knows-who (I can't remember and I hate it). It is a language somewhat simular to regular expressions, to determine the "pronouncement category" of a word.

SELECT soundex('jarred');
SELECT soundex('jarret');
SELECT soundex('jarrit');

All gives the same result, 'J630'.

However, soundex('hello') and soundex('yello') returns (respectivly) H400 and Y400. I'm not going into further details (search the web if you need them). Basically, you should either match the entire string, or cut out the leading character. This can produce some pretty fuzzy searches, but is NOT good for foreign languages (I have proven it not to be very compatible with norwegian ;-)

hint: SELECT SOUNDEX(field) FROM table

Read the mysql documentation, I think it has something about soundex.

dsinicco

Another way to do this would be to just take the first few letters of the search and use that in a "where fname like '%search%'" statement.

For example, if the person types "Jerret" for the search criteria, you can take the first 3 letters and use that as your actual search criteria. Then the search query would be like the following:

SELECT * from table where fname like 'Jerr%';

That would return all records where fname starts with 'Jerr' (and 'jerr'). You could also insert a '%' before the name to include any records that have 'Jerr' (or 'jerr') anywhere in the string.

Anon

Thanks for the help but how could i do this using variable names.
For example if i have a variable called fname which stands for first name and in my search i search for whatever fname is how could i apply those tatics you described.

Thanks again

Ira