Match start of any word

[themire]

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

[Barand]

$search = 'and';

 

then

 

"...WHERE stringfield LIKE '%$search%' "

[aschk]

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

[themire]

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

[aschk]

Better change to the MyISAM type then...