Ed Wagner (1/21/2013)
Just as a word of caution here, if you put your DECRYPTBYKEY and CONVERT functions to the left of the operator, they'll both have to run on every row in the table to be able to perform the LIKE comparison and return the result set. I've found the performance impact of doing this to be significant.
But with an encrypted column, you can't do this in another way. With = you can encrypt the searched value and search for equality.