CODEWITHSUNDEEP
androidsqlite
Charlie
Charlie

Reputation: 152

How to iterate a text string in SQLite?

I am not an expert in databases but I have to deal with one in a new app for Android I am just creating, which includes SQLite. I have 2 tables, let's call them Words and Chars. Words mainly register words the user enters, like a dictionary. Chars is meant to register any new single character included in the words added to the Words table and count the number of times any character is included in the Words table.

Having this scenario I decided creating a trigger to update the Chars table everytime a new word is added/deleted/modified in the Words and it seems to work as expected, my question is now, how should I iterate over any character in a newly entered word? I found that there is a FOR .. IN .. LOOP statement in SQL but as far as I can appreciate it is not available for SQLite. Any suggestion?

This is the script I created to generate from scratch the database, but it is not complete as it is not registering the times any character is appearing but just registers them. In addition, it is complaining for the "FOR" keyword in the trigger definition.

CREATE TABLE Words (id_word INTEGER PRIMARY KEY AUTOINCREMENT, meaning TEXT NOT NULL);
CREATE TABLE Chars (id_char INTEGER PRIMARY KEY AUTOINCREMENT, char TEXT UNIQUE);

CREATE TRIGGER NewWord BEFORE INSERT ON Words BEGIN
  FOR i IN 1..length(NEW.meaning) LOOP
    INSERT INTO Chars (char) VALUES (SUBSTR(NEW.meaning,i,1));
  END LOOP;
END;

Thanks

Upvotes: 2

Views: 426

Answers (1)

CL.
CL.

Reputation: 180070

As an embedded database, SQLite does not have control flow statements like FOR, but expects the containing application to implement all control logic.

Unfortunately, Android's built-in SQLite API does not allow registering user-defined functions, so you will not be able to do the character updates in a trigger.

Update the Chars table manually from your app whenever words are inserted.

Upvotes: 1

Related Questions