MySQL LIKE operator problem with Turkish characters

ercantunc
ercantunc used Ask the Experts™
on
In my sql  selecting ü (u with dots) also lists me u when I use LIKE operator. Plase see attachment
How can I do it only ü (u with dots) from mysqladmin.
also when I send the query from PHP do I have to do something extra?

Thank you
1.jpg
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
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;

Author

Commented:
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)

Commented:
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.

CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

Author

Commented:
Still returns empty, I use PHP

Commented:
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";
 
 
?>

Open in new window

Commented:
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;

Author

Commented:
Only inserts value test1

Commented:
What about this insert statment?

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

Author

Commented:
Again, inserted only test1

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

Author

Commented:
I am using phpmyadmin panel

Commented:
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.
I have solved by changing the collation to utf8_turkish_ci of related fields

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial