CODEWITHSUNDEEP
sql
user3982143
user3982143

Reputation:

Unable to query data using like with lots of special characters(%) in MySQL

I inserted the value below to a users table,

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

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('~`!@%23$%^%26*()-=_+{}[]|\\\\:;\\"\'<>?,./'); 
insert into users(name) values('test');

Query

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

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

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

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

What expected

When execute

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

It supposed to get this record only

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

Upvotes: 1

Views: 191

Answers (2)

Jesusbrother
Jesusbrother

Reputation: 513

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
'%' || '~`!@%23$%^%26*()-=\_+{}[]|\\\\:;\\"\'<>?,./' || '%';

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 
 '%~`!@%23$#%^#%26*()-=\_+{}[]|\\\\:;\\"\'<>?,./%' --<< escape # before % character
escape '#';

Here's new dbfiddle

Upvotes: 1

Jonas Metzler
Jonas Metzler

Reputation: 6005

You could use INSTR:

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

Upvotes: 0

Related Questions