I created a table in a database with this schema:
CREATE TABLE vocabulary (
id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
word TEXT NOT NULL,
reading TEXT,
meaning TEXT,
type TEXT,
jlpt INTEGER,
jtype TEXT);
This is for a vocabulary list I am working on. Now, I recently added the jtype TEXT, and this is where the problem lies.
I'm trying to show all elements with a certain jtype.
If I do this query
SELECT word FROM vocabulary WHERE jtype = "kanji";
I expect a list of words with this jtype. About 14k to be exact.
However there are no results when I run this query.
If I check for distinct jtypes like this:
SELECT DISTINCT jtype FROM vocabulary;
I get:
+----------+
| jtype |
+----------+
| katakana |
| kanji |
| hiragana |
| romaji |
+----------+
So the data does exist.
If I do:
SELECT word, jtype FROM vocabulary ORDER BY RANDOM() LIMIT 3;
The result is:
+------+----------+
| word | jtype |
+------+----------+
| ボーイ | katakana |
| 兄貴 | kanji |
| 練習 | kanji |
+------+----------+
So the rows do have both word and jtype category.
However a query for it results in nothing.
I have tried making sure there are no trailing spaces, but the original data from which the database is created does not have any spaces (I wrote the data and the database creation and double checked them) and adding spaces to the search term also does not fix the issue.
I am completely at a loss since so far everything else has worked. If I do this, for example:
SELECT word FROM vocabulary WHERE jlpt = 4;
I get the expected result. It is only in the column jtype where I can not find anything when looking for it directly.
If anybody could give me a hint or push me in the right direction, I would be very grateful.
Thank you for your time.
Edit: Reformatted the question, I hope it is more to the point. I heard that this site is kinda strict and a bit unfriendly to newcomers sometimes, so I had a lot of anxiety about posting questions here. If I make mistakes, please tell me. Thank you.