前提

・PHP 5.6.38
・SQL Server 12.0.2000.8
・対象のテーブルとデータ
MyCats

登録の際はphp側でデータを変換してからSQLでinsertしています。

sample.php

$name = mb_convert_encoding($name, 'UTF-16LE','UTF-8');
$name = bin2hex($name);
$name = '0x' . $name;

INSERT
 MyCats(ID, Name)
VALUES
　(4, CONVERT(varbinary(MAX), N'{$name}', 1));

方法

やはりまずphpで変換します。

sample.php

$name = mb_convert_encoding($name, 'UTF-16LE','UTF-8');
$name = bin2hex($name);
$name = '0x' . $name;

そして

SELECT ID, Name FROM MyCats WHERE Name LIKE '%' + CONVERT(NVARCHAR(32), {$name}) + '%';

っていうのでできました。
他にもやり方あるのかもしれませんが、一応これでできましたという一例でした。