MySQL LIKE operator problem with Turkish characters

Try this.  The X'CF' is a hex value the u with 2 dots.

select *
from contact
where name like concat('%',X'CF','%')
and orid = 24 limit 0,30;

It says:
MySQL returned an empty result set (i.e. zero rows). ( Query took 0.0010 sec )

The other thing is this is a typical query that the user makes from my site, so they can query some other Turkish characters. I need a solution (if exists) more robust without manipulating the query (like settings of mysql)

Try

select * from contact where name like concat('%',X'FC','%') and orid = 24 limit 0,30;

what programming language are you using for you website?  You can take the query the build and turn the criteria into a hex value.

Still returns empty, I use PHP

If you are using php you could do something like this

<?php
 
function ascii2hex($ascii) {
  $hex = '';
  for ($i = 0; $i < strlen($ascii); $i++) {
     $byte = strtoupper(dechex(ord($ascii{$i})));
     $byte = str_repeat('0', 2 - strlen($byte)).$byte;
     $hex.=$byte."";
   }
   return $hex;
}
 
$tmp = "thompson";
 
$sql = "select * from users where last_name like concat('%',X'".ascii2hex($tmp)."','%')";
 
echo "$sql";
 
 
?>

Select allOpen in new window

Not sure why it work for you it work for me

try these statements and see if it returns a row

create table test (test_field varchar(10));
insert into test values(concat('test1',X'FC','test2'));
select * from test where test_field like concat('%',X'FC','%') limit 0,30;

Only inserts value test1

What about this insert statment?

insert into test values(concat('test1',UNHEX('FC'),'test2'));

Again, inserted only test1

Are you running the statements from something like cpanel?  I am running the statements from a command line and they work fine.

I am using phpmyadmin panel

not sure if phpmyadmin is messing with the concat function or the UNHEX function, but it doesn't seem to work.  I do not have access to phpmyadmin to test, but works from my command line.  

Maybe related as to why your initial query won't return the proper rows.