CODEWITHSUNDEEP
mysql
Harwee
Harwee

Reputation: 1650

MySQL returns no results when searching a column

Here is the command I am using:

SELECT * FROM pos WHERE word = 'accelerator' OR id = 3;

This returns:

'3', 'accelerator\r', '1', NULL, NULL, NULL, NULL, NULL, NULL, NULL

But when I use:

SELECT * FROM pos WHERE word = 'accelerator';

I get NULL as response.

Here is my table structure

enter image description here

Upvotes: 0

Views: 43

Answers (4)

Lajos Arpad
Lajos Arpad

Reputation: 76591

Let's take a look at your where clause:

WHERE word = 'accelerator' OR id = 3;

this is true if at least one of the two operands is true. The condition is true for the given record, therefore at least one of the operands is true for it. Let's take a look at your other where clause:

WHERE word = 'accelerator';

this one is more strict and does not yield the given record. Using the second where clause you see there is no row with 'accelerator' as the value for word, however, you have found a record using your first where clause, which has 'acclerator\r' as its word, which differs from 'accelerator', but its id is 3, so, according to your first where clause, the record fulfills your condition, as at least one of the operands of the OR is true.

Upvotes: 0

Inigo Flores
Inigo Flores

Reputation: 4469

In 'accelerator\r' notice the \r at the end of the string.

This should work: 

SELECT * FROM pos WHERE word LIKE 'accelerator%'

Upvotes: 0

JuniorCompressor
JuniorCompressor

Reputation: 20025

'accelerator' is different from 'accelerator\r'. So it seems you have inserted the data without stripping them. To verify use:

SELECT * FROM pos WHERE word = 'accelerator\r'

It should return the row

Upvotes: 2

Martin
Martin

Reputation: 22760

'accelerator\r' !== 'accelerator'

You may be instead thinking of using LIKE so:

SELECT * FROM pos WHERE word LIKE '%accelerator%';

would return the correct results for you.

Upvotes: 0

Related Questions