0

I want a query to return rows with the column having entries like following:

abcda%aaa, pa%rdeep, sonisa%b etc.

Tried using following but doesn't get the desired results.

select * from table where column like "%a%%"

I want correct replacement for above so as to search for only those rows having "a%" as an entry for the column in the table not those starting with a.

Kamil Gosciminski
  • 16,547
  • 8
  • 49
  • 72
Pardeep
  • 1
  • 2

2 Answers2

2

If you want to escape % then You have to use backslash \

SELECT * FROM table_name WHERE `column_name` LIKE '%a\%%'.

Reference
Community
  • 1
  • 1
Raunak Gupta
  • 10,412
  • 3
  • 58
  • 97
1
create table j1
(   id int auto_increment primary key,
    thing varchar(100) not null
);

insert j1(thing) values ('abcda%aaa'),('fish'),('pa%rdeep'),('cat'),('sonisa%b'),('a'),('a\\%');


select * from j1 where thing like '%a\%%';
+----+-----------+
| id | thing     |
+----+-----------+
|  1 | abcda%aaa |
|  3 | pa%rdeep  |
|  5 | sonisa%b  |
+----+-----------+

Please see MySQL String Literals

Drew
  • 24,851
  • 10
  • 43
  • 78
  • Hi @Drew. Thanks for the reply. But my problem is that my values are not static meaning to say that I don't have a clue where to put percentage sign for solving this because data is coming from a variable and I only have the option to use the same variable in my java code by appending percent('%') symbol as var = "%"+var+"%" and var may be anything and issue arises when`%` is present in the var itself – Pardeep Oct 05 '16 at 17:34
  • I thought you wanted stuff that had an embedded % like a% or jason% – Drew Oct 05 '16 at 17:38
  • actually var may be anything i.e the index of `%` cannot be guessed. so how come I use`\` before `%` – Pardeep Oct 05 '16 at 17:40
  • so if you have an embedded % like jason% and you want to find all that have jason%, then do the above with % + (your thing like jason%) + % – Drew Oct 05 '16 at 17:42
  • but focus on the slash escape sequence just after jason – Drew Oct 05 '16 at 17:42
  • I dont know at which index to use escape as var may b anything like aaa%a, or aaa% or %aa etc. – Pardeep Oct 05 '16 at 17:46
  • Actually, I a newbee at stackoverflow. Can I have your mail so that we can chat over there? – Pardeep Oct 05 '16 at 17:48
  • A more general solution could be `where thing like concat('%', replace('anyStringContaining%', '%', '\\%'), '%');` – Paul Spiegel Oct 17 '16 at 21:16