10

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?

Lukas Eder
  • 196,412
  • 123
  • 648
  • 1,411

4 Answers4

8

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)
Lukas Eder
  • 196,412
  • 123
  • 648
  • 1,411
5

If its a single character you want to repeat, you can use printf function.

Bellow is an example where * is repeated 10 times.

sqlite> select printf('%.' || 10 ||'c', '*');
**********

To repeat multiple characters please see Lukas's answer above.

Shiplu Mokaddim
  • 54,465
  • 14
  • 131
  • 183
4

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)
Steve Broberg
  • 4,062
  • 3
  • 25
  • 37
3

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
jaimet
  • 103
  • 7
  • 1
    I've benchmarked your approach and compared it with the [`zeroblob`](https://stackoverflow.com/a/51792334/521799) approach. [Yours seems to be slightly faster on SQLite 3.30.1 via JDBC](https://github.com/jOOQ/jOOQ/issues/10157) – Lukas Eder May 05 '20 at 08:07
  • N.B. The replace approach uses less opcodes (10) vs printf (14). – user1461607 Aug 05 '21 at 11:20