Write a SQL query to print words that do not contain '%' symbol in a table

Question

I wrote the following query:

SELECT * from TABLENAME WHERE COLUMNNAME NOT LIKE ('%%%');

Assume that the table has only one column. The problem is that the query doesn't recogonize the % inside NOTLIKE as a character, rather as a part of the syntax itself. How do I rectify this?

I was thinking of replacing the `%` with something else then use that in the condition but I like @Akina answer better! — FanoFN, Dec 10 '20 at 08:15
In general - never use LIKE when you need to test for definite literal. It is designed for patterns only. — Akina, Dec 10 '20 at 08:16

score 3 · Accepted Answer · answered Dec 10 '20 at 08:29

3

You have the option of escaping characters:

SELECT * from TABLENAME WHERE COLUMNNAME NOT LIKE '%\%%';

Or:

SELECT * from TABLENAME WHERE COLUMNNAME NOT LIKE '%|%%' ESCAPE '|';

answered Dec 10 '20 at 08:29

Damien_The_Unbeliever

234,701
27
340
448

This worked just fine. Thank you! – Varun Sundararajan Dec 10 '20 at 14:59

score 2 · Answer 2 · answered Dec 10 '20 at 08:27

2

you can use []

SELECT * from TABLENAME WHERE COLUMNNAME NOT LIKE ('%[%]%');

answered Dec 10 '20 at 08:27

ali sadeghi

121
3

good to you, perfect! – Mohammad Dec 10 '20 at 08:29
May I know which MySQL version can do this? – FanoFN Dec 10 '20 at 08:31
it used in sql server,in mysql i don't know – ali sadeghi Dec 10 '20 at 08:39
1

mysql does not support this – ysth Dec 10 '20 at 09:15

score 1 · Answer 3 · answered Dec 10 '20 at 08:24

1

This is what I had in mind:

SELECT * from TABLENAME WHERE REPLACE(COLUMNNAME,'%','findthis') NOT LIKE ('%findthis%');

By replacing the symbol with something else then use it as the condition in NOT LIKE.

answered Dec 10 '20 at 08:24

FanoFN

6,815
2
13
33

1

`'findthis'` may occur in the value. The literal which is absent in the value with guarantee must be used. I prefer `CHAR(0)`... – Akina Dec 10 '20 at 09:14
I take note of that @Akina. Thanks! – FanoFN Dec 11 '20 at 05:28

Write a SQL query to print words that do not contain '%' symbol in a table

3 Answers3