However I recall there is some scenario that does not allow a leading wildcard. Can't exactly remember what right now. Maybe the Find or Filter on ADO recordsets?
At one time you could not use leading wildcards in ADO recordsets, but I believe you can now.
As with other responders I avoid leading wildcards like the plague (although I accept there are occasions where they are unavoidable) - most users will know whatever they are looking for starts with and I train them to include a wildcard if necessary. Even starting with an initial letter before including a * will be roughly 26 times faster then just starting with a * - assuming the field is indexed of course, otherwise I guess it doesn't matter since indexed fields will be searched sequentially.
with regards using Like with numbers - yes it works, however I don't know whether indexing is used per se since the number is not stored as separate characters but as its binary equivalent.