In my tests, it looks as though the underscore character ( _ ) works as a wildcard for a single character, and % works for a wildcard string.
So for example, in a datagrid with a search field on Lastname (set to 'Contains' in the modeller), I can enter s%r in the search field and return results like
Bhaskar Ensrude Requester Sanders Spector Support User
This is using MS SQL server as the DB
No, there is no wildcard support.
As you mention yourself, you can use operator such as contains: