WHERE condition is true but not all rows are returned? (SQLite db)

Asked Oct 03 '21 at 10:12

Active Oct 03 '21 at 11:00

Viewed 40 times

When I try to use WHERE word = "a" there is only 1 row returned when in fact there are 2 rows with the word "a". As you can see in the second picture there is also another "a" with the id = 1. How can I query both of them?

Only 1 row is returned with WHERE word = "a"

There are actually 2 rows with for the word "a"

UPDATE: They actually have different length, one 1 and the other 2.

edited Oct 03 '21 at 11:00

asked Oct 03 '21 at 10:12

PainterHalver

Perhaps there are spaces after the `a` in the other row? – Lasse V. Karlsen Oct 03 '21 at 10:19
What if you use like "%a%", and what does length(word) show? – Stu Oct 03 '21 at 10:38
They actually have different length, now how can I query both of them :( – PainterHalver Oct 03 '21 at 11:01
Update the table to remove the trailing spaces: `UPDATE av SET word = TRIM(word)` – forpas Oct 03 '21 at 11:06
@forpas I tried but the the length still stayed 2. – PainterHalver Oct 03 '21 at 11:25
Your data probably contains other non-printable characters such as tab or return etc; does `like "%a%" find exactly the two rows expected? – Stu Oct 03 '21 at 11:28
@Stu It does but it will also return other different rows that matches "%a%". I need a way to find the exact "a". – PainterHalver Oct 03 '21 at 11:40
Look at [this article](https://stackoverflow.com/questions/11604766/sqlite-char-ascii-function) then for how to identify other characters that you need to *replace* – Stu Oct 03 '21 at 11:42

WHERE condition is true but not all rows are returned? (SQLite db)

0 Answers0