CODEWITHSUNDEEP
mysqlsql
user3419642
user3419642

Reputation: 27

SQL LIKE statement not working

I have a column in a table containing gene names. Example of gene names are dnaA, BN_001,BN_0023 etc...

I want to have all genes names that do not contain the underscore. The statement I used was this:

    SELECT Gene from cds WHERE Gene NOT LIKE '%_%';

However, this does not return the correct names of genes. What is the problem? I am using MYSQL as RDBMS.

Thank you

Upvotes: 1

Views: 127

Answers (7)

Nagaraj S
Nagaraj S

Reputation: 13484

Use this '%\_%'

SELECT Gene from cds WHERE Gene NOT LIKE '%\_%';

Working Fiddle

Upvotes: 1

sumgeek
sumgeek

Reputation: 76

You should write 

LIKE '%\_%' ESCAPE '\'

because _ mean something in SQL, so you have to escape this by \

Upvotes: 2

dnoeth
dnoeth

Reputation: 60462

You need to escape the underscore, MySQL seems to support Standard SQL's ESCAPE

SELECT Gene from cds WHERE Gene NOT LIKE '%\_%' ESCAPE '\';

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269773

In MySQL, the underscore represents any single character in the like pattern. You can read the documentation on this, but here is the relevant part:

With LIKE you can use the following two wildcard characters in the pattern.

Character   Description
%   Matches any number of characters, even zero characters
_   Matches exactly one character

For this, just use a function like locate():

where locate('_', gene) = 0

Upvotes: 1

user3278460
user3278460

Reputation:

_ is special character for LIKE operator. You must escape it, please try Gene NOT LIKE '%\_%';

Upvotes: 1

mrida
mrida

Reputation: 1157

SELECT Gene from cds WHERE Gene NOT LIKE '%\_%';

Upvotes: 1

juergen d
juergen d

Reputation: 204766

The problem is that _ is a placeholder in ther like statement of any single character.

Instead you can use

SELECT Gene from cds 
WHERE instr(Gene, '_') = 0

Upvotes: 1

Related Questions