How to run a MySQL query in phpMyAdmin with LIKE clause on a VARBINARY column?

Question

I am attempting to query a MySQL table that should filter results on a VARBINARY column using PHPmyAdmin, however a query such as SELECT * FROM 'cg_mclean_refs' WHERE mediaUrl LIKE("%Hello%") will return 0 results.

I have attempted also a code sample found here , but this throw errors, it looks like there's no support for preparedstatemnts in PHPmyAdmin.

String query="SELECT * from T WHERE col LIKE ?";
PreparedStatement st = connection.prepareStatement(query); // Assuming I already have connection object
byte[] prefixBytes = somePrefixBytesIWouldLikeToSearchFor;
String likeString = new String(bytes) + "%";
st.setString(1, likeString);
st.executeQuery();

So, is there a way to use a LIKE statement converting text to VARBINARY within the query?

Answer 1

When the column is VARBINARY then binary collation is used during compare. The query will find 'Hello, John' but will not find 'hello, Jim'. But you may specify needed collation explicitly.

DEMO

I get this error

Error Static analysis: 2 errors were found during analysis.

Unrecognized keyword. (near "COLLATE" at position 64)

Unexpected token. (near "utf8mb4_0900_ai_ci" at position 72)

SQL query: Copy Documentation SELECT * FROM cg_mclean_refs` WHERE mediaUrl LIKE '%Hello%' COLLATE utf8mb4_0900_ai_ci

MySQL said: Documentation #1273 - Unknown collation: 'utf8mb4_0900_ai_ci'

Check your current charset and available collations for it. Edit explicit collation specifying accordingly.