If you start using Transfer ORMenough you will start finding situations where you need to write some SQL. I know you thought the Transfer (or any ORM for that matter - CF9) would be the end of writing SQL. Well its not in fact when you do have to write some, it's not all that bad. Tonight I was writing a search feature for my shopping cart application when I realized I have never done a like comparison in TQL. It turns out its really no different than any other TQL I have written before.
If you take a look at the Transfer docs for Retrieving query lists you will not a listByQuery method. This method allows you create a transfer query and return the results based on it. This is important to note because its not a query object or some sql that you wrote, its a transfer query. If your not familiar with transfer queries or TQL I suggest you read up on them.
So now we know the method we need to use, let's write some code. We are using a simple text box for the user to type in their search term with the name keyword. When the form is submitted we are creating a transfer query using some TQL. The important thing to note is that we don't use variables in TQL, we actually use a parameter and we will replace it later. Once we have our query we can replace those params using the setParam method. In the set param we can use our variable just how we would in a like statement. When that is done we can pass our query into our transfer list by query method and it will return a query with our search results.