0

So if I do this query in MySQL 5.5:

SELECT mycol FROM mytable WHERE mycol LIKE '%Â'

I get all record where mycol ends with any variant of A, so I get

LA
la
LÂ

How can I modify this query so I only get the results that end with the specific special character Â

show create table mytable

CREATE TABLE `direcciones` (
 `idusuario` varchar(50) NOT NULL,
 `iddireccion` int(10) NOT NULL AUTO_INCREMENT,
 `calle` varchar(150) NOT NULL, **-- THIS IS MY FIELD**
 `numero` int(4) NOT NULL,
 `piso` varchar(10) NOT NULL,
 `puerta` varchar(10) NOT NULL,
 `localidad` varchar(100) NOT NULL,
 `provincia` varchar(100) NOT NULL,
 `cp` int(5) NOT NULL,
 PRIMARY KEY (`iddireccion`)
) ENGINE=InnoDB AUTO_INCREMENT=4192 DEFAULT CHARSET=latin1


show variables like '%character_set%'



character_set_client    utf8    
character_set_connection    utf8    
character_set_database  utf8    
character_set_filesystem    binary  
character_set_results   utf8    
character_set_server    latin1  
character_set_system    utf8    
character_sets_dir  /usr/share/mysql/charsets/
Jack Casas
  • 914
  • 18
  • 37
  • Check this thread: https://stackoverflow.com/questions/500826/how-to-conduct-an-accent-sensitive-search-in-mysql – forpas Aug 13 '20 at 17:03

1 Answers1

3

To keep mysql from using the column's collation setting, which is what allows those matches, specify that your string is binary:

SELECT mycol FROM mytable WHERE mycol LIKE BINARY '%Â';

or

SELECT mycol FROM mytable WHERE mycol LIKE CAST('%Â' AS BINARY);
ysth
  • 96,171
  • 6
  • 121
  • 214
  • No sure about what is going on, but I'm not getting any results with this query. Using phpMyAdmin as client – Jack Casas Aug 14 '20 at 09:31
  • show output of `show create table mytable` and `show variables like '%character_set%'`? – ysth Aug 14 '20 at 17:12
  • question updated. Our team messed up collations when creating the database, but right now, this is the situation we have. We yet to plan and test the database upgrade with a homogenous collation, but as users, password, etc are involved, we need to plan this carefully – Jack Casas Aug 16 '20 at 11:16