Page 1 of 1
MATCH and LIKE
Posted: Mon Jun 27, 2005 8:30 pm
by dardsemail
Hi,
I've been trying to find some information on 'MATCH' and 'LIKE' - but I'm not 100% sure how to use them both correctly.
I would like to be able to search fields within a database on partial words if possible - I'm assuming that this would be done using both MATCH and LIKE. Is this the case?
Code: Select all
"e;SELECT * FROM table_tb WHERE MATCH (FieldName) AGAINST ($queryString) ORDER BY FieldName ASC"e;
This is what I have for my match statement. Is it possible to add a 'LIKE' parameter to this statement? If there are any good search tutorials, please feel free to point me to them!
Thanks!
Posted: Mon Jun 27, 2005 9:54 pm
by Burrito
I've only used match/against on full text indexed fields. That isn't to say that it won't work on other fields (I don't know :S), but that's the practice I've used.
with 'like' you have to actually match the string you're searching:
ex:
Code: Select all
select * from myTable where field like 'bob'
//will only match if your field = bob
select * from myTable where field like 'bob%'
//will match any row that starts with "e;bob"e;
select * from myTable where field like '%bob%'
//will match any row that has bob anywhere in it
as you can see you have to have the wildcard "%" in the select statement in order to match anything other than just the literal string. That being the case, you'd have to inject them into your search strings which can prove to be a PITA sometimes. This is where using full text indexed searches comes in really handy (the match against query you have posted).
I'd be interested to find out if you can use match/against on other fields (ones that aren't full text indexed)
Posted: Tue Jun 28, 2005 9:35 am
by dardsemail
You actually get an error if you try and do a MATCH against fields that aren't full-text indexed - so that solves that.
I appreciate your feedback on the 'LIKE' stuff. I've never used it before. This search stuff is pretty new to me so I'm really fumbling around in the dark.
I guess I could do a MATCH query and then a separate LIKE query - though it sounds like the LIKE query might be the best option. I'll play around and see what I get.
Thanks so much!