How to Emulate Repeat() in SQLite

How to emulate REPEAT() in SQLite

My answer combines Shiplu Mokaddim's "printf character substitution repetition" with the "replace" of Steve Broberg and Lukas Eder:

sqlite> SELECT replace(printf('%.' || 5 || 'c', '/'),'/','My string ');
My string My string My string My string My string      

It's also easy to derive the number of repetitions from table data. Here's an example using a common table expression:

sqlite> WITH cte(string, reps) AS
    ..>   (SELECT * FROM (values ('alpha ', 1),('bravo ', 5),('charlie ', 3) ) )
    ..> SELECT *, replace(printf('%.' || reps || 'c', '/'), '/', string) FROM cte;
alpha       1           alpha
bravo       5           bravo bravo bravo bravo bravo
charlie     3           charlie charlie charlie

How to emulate LPAD/RPAD with SQLite

A simpler version of @user610650's solution, using hex() instead of quote(), and works with string padding in addition to char padding:

X = padToLength
Y = padString
Z = expression

select
    Z ||
    substr(
        replace(
            hex(zeroblob(X)),
            '00',
            Y
        ),
        1,
        X - length(Z)
    );

Sqlite column with repetitive values

The question is very similar to Add data to many-to-many relation with one SQL command, but it also discusses further aspect - automatic clean-up of unused entities.

Is there a way to let SQLite do everything automatically? ... (without having to maintain anything ourselves)

No. You basically want to insert rows in the base table and referenced table if it doesn't exist yet, while specifying the reference by value rather than its surrogate key. That in fact isn't a straightforward task in other RDBMSes as well. Some of them support:

• Stored procedures
• Multitable inserts
• OUTPUT clause
• RETURNING clause
• Writable (updatable) views
• INSTEAD OF triggers on views

From the above list SQLite only supports INSTEAD OF triggers. Here's how it applies to your use case (I have adopted table words from your db<>fiddle mentioned in comments under the question and renamed its column a to value):

PRAGMA foreign_keys = ON;

CREATE TABLE words(
  id INTEGER PRIMARY KEY,
  value TEXT
);

CREATE UNIQUE INDEX unique_words_value ON words(value);

CREATE TABLE data(
  id INTEGER PRIMARY KEY,
  word_id INTEGER NOT NULL,
  FOREIGN KEY (word_id) REFERENCES words(id)
);

CREATE VIEW data_view AS
SELECT d.id, w.value FROM data AS d INNER JOIN words AS w on w.id = d.word_id;

CREATE TRIGGER data_view_insert INSTEAD OF INSERT ON data_view
BEGIN
  INSERT OR IGNORE INTO words(value) VALUES (NEW.value);
  INSERT OR IGNORE INTO data(word_id) VALUES(
    (SELECT id FROM words WHERE value = NEW.value)
  );
END;

INSERT INTO data_view (value) VALUES
  ('random1'),
  ('random2'),
  ('random3'),
  ('random1'),
  ('random3'),
  ('random4');

The INSERT statement produced this content of table words:

REPLACE(HEX(ZEROBLOB(8)), '00', '[A-HJ-NPR-Z0-9]')

[A-HJ-NPR-Z0-9][A-HJ-NPR-Z0-9][A-HJ-NPR-Z0-9][A-HJ-NPR-Z0-9][A-HJ-NPR-Z0-9][A-HJ-NPR-Z0-9][A-HJ-NPR-Z0-9][A-HJ-NPR-Z0-9]

CHECK(myfield GLOB 
                REPLACE(HEX(ZEROBLOB(8)), '00', '[A-HJ-NPR-Z0-9]') || 
                '[X0-9]' || 
                REPLACE(HEX(ZEROBLOB(8)), '00', '[A-HJ-NPR-Z0-9]')
)

   CASE WHEN position = 0
        THEN INSTR(string, substring)
        WHEN position > 0
        THEN INSTR(SUBSTR(string, position), substring) + position - 1
        WHEN position < 0
        THEN LENGTH(RTRIM(REPLACE(string,
                                  substring,
                                  REPLACE(HEX(ZEROBLOB(LENGTH(substring))),
                                          '00',
                                          '¬')),
                          string)) - LENGTH(substring) + 1
   END