Searching text with SQL

Database with various text fields. When user searches the site wish to check 3 of those fields for the matching search term [name, filename, comment]. Basic level just needs to return any rows that match the search term in any of the 3 fields. The fields are all short no more than 256 characters.

What would be the most practical and efficent way to implement this in SQL? Looking for things like datatypes, type of search query(LIKE etc.) or any other ideas like a new combined field.

Already have code in and working fine but curious to learn better ways to implement it from programmers stronger at SQL.

>>1
If you're using the piece of shit known as MySQL, use fulltext indexes.

http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html