Most relational databases have some sort of REPEAT() string function, for instance:
SELECT REPEAT('abc', 3)
Would yield
abcabcabc
SQLite on the other hand has a very limited feature set. The functions supported by SQLite are listed here:
http://www.sqlite.org/lang_corefunc.html
Can REPEAT() be emulated with the functions available in SQLite?
A solution was inspired by this answer to a related question, here:
How to emulate LPAD/RPAD with SQLite
I wanted to share this on Stack Overflow, as this may be useful to other SQLite users. The solution goes like this:
-- X = string
-- Y = number of repetitions
replace(substr(quote(zeroblob((Y + 1) / 2)), 3, Y), '0', X)
If its a single character you want to repeat, you can use printf function.
Bellow is an example where x is repeated 10 times. The key is the * in the format string which specifies that the width of the field will be passed as a parameter:
sqlite> select printf('%.*c', 10, 'x');
xxxxxxxxxx
To repeat multiple characters you can then replace() each x by the longer string, much as in Lukas's answer above.
A simplified version of @Lukas Eder's solution using hex() instead of quote:
-- X = string
-- Y = number of repetitions
replace(hex(zeroblob(Y)), '00', X)
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
