-- 1. Create table for single words
CREATE TABLE single_words (
id INTEGER PRIMARY KEY AUTOINCREMENT,
word TEXT,
bangla TEXT
);
-- 2. Insert only single-word entries (no spaces)
INSERT INTO single_words (word, bangla)
SELECT word, meanings FROM tbl_medical
WHERE word NOT LIKE '% %';
-- 3. Replace space with ✔ (optional, usually skipped for single words)
UPDATE single_words SET word = REPLACE(word, ' ', '✔');
-- 4. Create table for two-word entries
CREATE TABLE two_words (
id INTEGER PRIMARY KEY AUTOINCREMENT,
word TEXT,
bangla TEXT
);
-- 5. Insert only two-word entries (exactly 1 space → 2 words)
INSERT INTO two_words (word, bangla)
SELECT word, meanings FROM tbl_medical
WHERE LENGTH(word) - LENGTH(REPLACE(word, ' ', '')) = 1;
-- 6. Replace space with ✔ in two-word entries
UPDATE two_words
SET word = REPLACE(word, ' ', '✔')
WHERE LENGTH(word) - LENGTH(REPLACE(word, ' ', '')) = 1;
-- 7. Revert ✔ back to space in both single_words and two_words tables
-- For single_words:
UPDATE single_words
SET word = REPLACE(word, '✔', ' ');
-- For two_words:
UPDATE two_words
SET word = REPLACE(word, '✔', ' ');
-- 8. Create table for three-word entries
CREATE TABLE three_words (
id INTEGER PRIMARY KEY AUTOINCREMENT,
word TEXT,
bangla TEXT
);
-- 9. Insert only three-word entries (exactly 2 spaces → 3 words)
INSERT INTO three_words (word, bangla)
SELECT word, meanings FROM tbl_medical
WHERE LENGTH(word) - LENGTH(REPLACE(word, ' ', '')) = 2;
-- 10. Replace space with ✔ in three-word entries
UPDATE three_words
SET word = REPLACE(word, ' ', '✔')
WHERE LENGTH(word) - LENGTH(REPLACE(word, ' ', '')) = 2;
-- 11. Revert ✔ back to space in three_words table
UPDATE three_words
SET word = REPLACE(word, '✔', ' ');
Get all table name:
SELECT name FROM sqlite_master WHERE type='table'
