themire Posted October 3, 2008 Share Posted October 3, 2008 Hi, I'm looking for an SQL query to match the start of any word in a list of words for an auto-suggest dropdown. For example, if the query is for 'and' I don't just want to have : WHERE LEFT ($string, 3)=='and' but match it to the start of any word in the string. So, therefore a search for 'and' would match with both 'and justice for all' AND 'jack and jill' Any help would be greatly appreciated, Regards Quote Link to comment https://forums.phpfreaks.com/topic/126876-match-start-of-any-word/ Share on other sites More sharing options...
Barand Posted October 3, 2008 Share Posted October 3, 2008 $search = 'and'; then "...WHERE stringfield LIKE '%$search%' " Quote Link to comment https://forums.phpfreaks.com/topic/126876-match-start-of-any-word/#findComment-656282 Share on other sites More sharing options...
aschk Posted October 3, 2008 Share Posted October 3, 2008 A better option might be to have a fulltext index on the field then using the fulltext lookups. SELECT [col1,[col2],[col3]...] FROM <table> WHERE MATCH(<field>) AGAINST ('$string') Using LIKE '%$search%' will cause a full table scan... = bad Quote Link to comment https://forums.phpfreaks.com/topic/126876-match-start-of-any-word/#findComment-656307 Share on other sites More sharing options...
themire Posted October 3, 2008 Author Share Posted October 3, 2008 Thanks. I'd rather not do the LIKE '%$string%' full-search. I've just realised the tables are InnoDB though which doesn't support FULLTEXT indices Quote Link to comment https://forums.phpfreaks.com/topic/126876-match-start-of-any-word/#findComment-656319 Share on other sites More sharing options...
aschk Posted October 3, 2008 Share Posted October 3, 2008 Better change to the MyISAM type then... Quote Link to comment https://forums.phpfreaks.com/topic/126876-match-start-of-any-word/#findComment-656324 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.