Unable to query data using like with lots of special characters in MySQL-CodePudding

I inserted the value below to a users table,

~\`!@#$%^&*()-=_ {}[]|\\:;\"'<>?,./

then query it with like, got nothing , however the = works correct, why the LIKE clause doesn't work please?

Prepare data

CREATE TABLE `users` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
   PRIMARY KEY (`id`)
);

insert into users(name) values('~`!@#$%^&*()-=_ {}[]|\\\\:;\\"\'<>?,./'); 
insert into users(name) values('test');

Query

select * from users where name like '%~`!@#$%^&*()-=\_ {}[]|\\\\:;\\"\'<>?,./%'; // Got nothing

select * from users where name='~`!@#$%^&*()-=_ {}[]|\\\\:;\\"\'<>?,./';  // Got the inserted data

Even though I escape the % and _, it still not work

select * from users where name like '%~`!@\#$\%^\&*()-=\_ {}[]|\\\\:;\\"\'<>?,./%'; // Still got nothing

What expected

When execute

select * from users where name like '%~`!@#$%^&*()-=\_ {}[]|\\\\:;\\"\'<>?,./%'; // Got nothing

It supposed to get this record only

~\`!@#$%^&*()-=_ {}[]|\\:;\"'<>?,./

CodePudding user response：

You can use concat like this (not sure MySQL or another DB type there, but the principle is the same):

select * from users 
where name like
'%' || '~`!@#$%^&*()-=\_ {}[]|\\\\:;\\"\'<>?,./' || '%';

Here's dbfiddle

upd. Due to a new issue with new rows, I got fixed my code with ESCAPE.

select *
from users 
where name like 
 '%~`!@#$#%^#&*()-=\_ {}[]|\\\\:;\\"\'<>?,./%' --<< escape # before % character
escape '#';

Here's new dbfiddle

CodePudding user response：

You could use INSTR:

SELECT * FROM users 
WHERE INSTR(name, '~`!@#$%^&*()-=\_ {}[]|\\\\:;\\"\'<>?,./') > 0