php - MySQL - using LIKE operator with UNHEX() function

Question

I'm trying to use MySQL function UNHEX() with operator LIKE but i can't get the expected result as $result_2_expected.

What i'm doing wrong?
I'm trying the ways bellow, all without success:

• LIKE UNHEX('%4272756365%')
• LIKE '%UNHEX('4272756365')%'
• LIKE '%UNHEX(4272756365)%'

Experience with Equal operator (Success):

$name_1 = bin2hex("Bruce Wayne"); //4272756365205761796e65
$query_1 = "SELECT `NAME` FROM `USERS_TABLE` WHERE `NAME` = UNHEX('{$name_1}')"; // UNHEX('4272756365205761796e65')


$result_1 = [0][NAME] => "Bruce Wayne"; // Success

Experience with LIKE operator (Fail):

$name_2 = bin2hex("Bruce"); // 4272756365
$query_2 = "SELECT `NAME` FROM `USERS_TABLE` WHERE `NAME` LIKE UNHEX('%{$name_2}%')"; // UNHEX('%4272756365%')

$result_2 = [] => null; // Fail

$result_2_expected = [0][NAME] => "Bruce Wayne"; [1][NAME] => "Bruce Willis"; [2][NAME] => "Bruce Springsteen"; // Expected as Success

question from:https://stackoverflow.com/questions/65918118/mysql-using-like-operator-with-unhex-function

Answer 1

Your problem is that you have put the wildcard characters into the call to UNHEX instead of concatenating them outside it, and that is making UNHEX return NULL, which will always cause LIKE to return false.

Change

LIKE UNHEX('%{$name_2}%')

to

LIKE  CONCAT('%', UNHEX('{$name_2}'), '%')

and your code will work as expected. Alternatively, you could do as @Akina suggested in the comments, and replace the % signs in the call to UNHEX with their hex representation (25) and replace your code with

LIKE UNHEX('25{$name_2}25')