0

I did a search with LIKE and REGEXP a special character "ñ", the results here:

CREATE TABLE IF NOT EXISTS `testCharacter` (
  `id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `name2` char(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `name3` text COLLATE utf8_unicode_ci
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=4 ;

INSERT INTO `testCharacter` (`id`, `name`, `name2`, `name3`) VALUES
(1, 'ññ', 'ññ', 'ññ'),
(2, 'ñn', 'ñn', 'ñn'),
(3, 'nn', 'nn', 'nn');

First test, LIKE

SELECT * FROM `testCharacter` where name like '%ñ%'

and same result for all columns (where name2 like '%ñ%', where name3 like '%ñ%') :

+--+-----+------+------+
|id| name| name2| name3|
+--+-----+------+------+
| 1| 'ññ'|  'ññ'|  'ññ'|
| 2| 'ñn'|  'ñn'|  'ñn'|
| 3| 'nn'|  'nn'|  'nn'|
+--+-----+------+------+

Second Test, REGEXP

SELECT * FROM `testCharacter` where name REGEXP '(ñ)'

and same result for all columns (where name2 like '%ñ%', where name3 like '%ñ%') :

+--+-----+------+------+
|id| name| name2| name3|
+--+-----+------+------+
| 1| 'ññ'|  'ññ'|  'ññ'|
| 2| 'ñn'|  'ñn'|  'ñn'|
+--+-----+------+------+

The question, LIKE no have difference between characters specials ('ñ' and 'n') and REGEXP yes?

And applies for characters with accents, like that á, é, í, ó, ú

Cœur
  • 37,241
  • 25
  • 195
  • 267
Carlos Rodriguez
  • 833
  • 8
  • 12
  • A well-known limitation of regexp support in MySQL is that it isn't multi-byte safe. – Álvaro González Apr 28 '14 at 16:22
  • BTW, the downvote is not mine. This might be a dupe but it's clearly a question with a lot of effort. Meanwhile, horrible "Doesn't work, tried google, help!!!" questions get +4 easily. – Álvaro González Apr 28 '14 at 16:28
  • Take a look here http://stackoverflow.com/questions/14137273/mysql-regexp-query-accent-insensitive-search – JSeven Apr 28 '14 at 16:39

0 Answers0