I have a database that have a copy of all emails from an imap server.
So I have a column name that is the sender's address, sometimes like this:
=?utf-8?B?UmVwb3J0Z
and others without encoding, and others with others encoding
=?iso-8859-1?Q?SALUDO_Y_CO
I think it is the mime encode for emails.
How can I search that column with an already decoded value? Example
SELECT * FROM tablename WHERE decoded_column(columnname) REGEXP '".$text_to_search."'
As @tadman already mentioned there is no built-in way of doing this.
You may try to use the following solution. Create a stored function that you will use later in your WHERE
clause.
CREATE FUNCTION quoted_printable(input VARCHAR(255)) RETURNS VARCHAR(255)
BEGIN
// Here the code for decoding your input
END
and then in your query:
SELECT * FROM tablename WHERE quoted_printable(columnname) REGEXP '".$text_to_search."'
Just keep in mind that stored functions have more limitations than procedures so you may need to call a procedure from you function to get what you need.
Having said that, I would still suggest to store the data in a sanitized way - as @tadman said convert them already to UTF-8 before you store them. The solution I proposed above should be treated just as an ugly workaround rather than a viable solution.