By adding a star after your $search, the query will match any pattern starting with $search, i.e. searching 'Smith' will match 'Smith' 'Smithy' etc. Unfortunately there's no way to match $search inside another pattern so searching 'at*' won't match 'data' for example.
If you need this, you might need to explode the search string (split it using a given delimiter, i.e. space), and then use an OR combination of LIKE clauses. For example, suppose you have a people database where you need to search over 3 fields: firstname, lastname and address. Someone searches for 'ob mit'. First you would split the query in two separate strings 'ob' and 'mit' and then the SQL query would look like:
WHERE UPPER(firstname + lastname + address) LIKE UPPER('%ob%')
OR UPPER(firstname + lastname + address) LIKE UPPER('%mit%');
That would concatenate the fields you need to search for and try to match them with each substring provided by the user. This should match something like:
[CODE]Firstname Lastname Address
Bob ... ...
Robert Smith ...
Mitch ... ...
... ... ... Observatory ...[/CODE]