Search in encrypted field in SQL server

See more:

SQL

encryption

encoding

Hello everyone!
I have a table in sqlserver, my table has a field as string, for example FirstName (it's just example!). This field "EncryptByPassPhrase" before insert on database.

Before encode: Ali
After  encode: 0x010000001D905174BB7947AE1C600A4AB564A123310F92C21C9A4221

Now i would search on this field for example i would get all fields that contains charcters 'Al'.
I can decode this field and search on such as:

select * from EncodeTest e
where  dbo.Decoder(e.FirstName) like '%Al%'

But this way very slow on many numbers of record.
Please help me for best solution.

Thanks.

What I have tried:

declare @EncodedName varbinary(max)
select @EncodedName =EncryptByPassPhrase('key', cast( @Name as varbinary(max)) )


declare @DecodedName nvarchar(max)
select @DecodedName=convert(varchar(100),DecryptByPassPhrase('key',cast( @EncodedName as varbinary(max)) ))

select * from EncodeTest e
where dbo.Decoder(e.FirstName) like '%Al%'