Search code examples
mysqlhexsql-likecase-insensitive

How can I unhex and make case-insensitive search in MySQL?

I am having some data in column Name MYDATA that is in HEX format. I want to do a case-insensitive search for keywords (eg:absent) and get the results in UNHex format. I am using below statement:

 SELECT ADDRESS , DESTINATION , UNHEX(MYDATA) FROM DataBaseName.TableName
 WHERE LOWER(UNHEX(MYDATA)) LIKE LOWER('%absent%')

Problem: The statement get accepted in query with no errors but returns zero records. Where as there is data with keywords similar to ABSENT and Absent in the database specified.

Solution 
  • SELECT ADDRESS , DESTINATION , CAST(UNHEX(MYDATA) AS CHAR) FROM DataBaseName.TableName WHERE LOWER(CAST(UNHEX(MYDATA) AS CHAR)) LIKE '%absent%'

    Try above query. As mentioned in answer, you can try using CAST.