SQL Commands

 

-- 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'