SQLite is so light, it doesn’t have any useful string functions. It doesn’t have ASCII(), LPAD(), RPAD(), REPEAT(), POSITION(), you name it. It does, however, have a wonderful RANDOMBLOB() function. So if you really need a good random number generator, use a SQLite database and generate a 1GB blob. That should give you a couple of random numbers for the next years.
For a full (or rather, empty) list see the SQLite function reference here:
http://www.sqlite.org/lang_corefunc.html
Function Simulation: REPEAT()
Not having any functions doesn’t mean that you can’t simulate them. You can. Take REPEAT(), for instance. Apart from the RANDOMBLOB(), you can also generate a ZEROBLOB(). It’s a blob with lots of zeros in it. But you can’t just go and do this:
-- Simulate REPEAT('abc', 3) replace(zeroblob(3), 0, 'abc')
That would be too easy. The problem with the zeroblob is, that when cast to a string, it is actually a zero-terminated string. Quite usual when programming in C. But hey, the first character is a zero, so the resulting string is terminated right at the beginning. How useful is that??
But here’s a trick, QUOTE() the ZEROBLOB(). That would escape all characters in hex format. In other words:
quote(zeroblob(3)) yields X'000000'
Nice. Now we’ve got three extra letters around twice as many zeroes as we wanted. So we’ll simply do this
-- Simulate REPEAT('abc', 3) replace(substr(quote(zeroblob(2)), 3, 3), '0', 'abc') -- Or more generally: X = 'abc', Y = 3 replace(substr(quote(zeroblob((Y + 1) / 2)), 3, Y), '0', X)
Doesn’t that start to make fun? Note, I have documented this simulation also here:
http://stackoverflow.com/questions/11568496/how-to-simulate-repeat-in-sqlite
Function Simulation: LPAD() and RPAD()
REPEAT() was easy. But REPEAT() was inspired by LPAD() and RPAD(), which is similar to REPEAT(), except that a character is padded to the left or right of another string, until a given length of the resulting string is reached. ZEROBLOB() will help us again! Let’s consider RPAD():
-- Simulate RPAD('abc', 7, '-') 'abc' || replace(replace(substr(quote(zeroblob(4)), 3, 4), '''', ''), '0', '-') -- Or more generally: -- RPAD() Using X = 7, Y = '-', Z = 'abc' Z || replace( replace( substr( quote(zeroblob((X + 1) / 2)), 3, (X - length(Z)) ), '''', '' ), '0', Y ) -- LPAD() Using X = 7, Y = '-', Z = 'abc' replace( replace( substr( quote(zeroblob((X + 1) / 2)), 3, (X - length(Z)) ), '''', '' ), '0', Y ) || Z
Now if this isn’t funky! This was actually something, I didn’t come up with myself. This was an answer I was given on Stack Overflow, where great minds spend lots of spare time on weird problems like this:
http://stackoverflow.com/questions/6576343/how-to-simulate-lpad-rpad-with-sqlite
Of course, these simulations will be part of the next version of jOOQ, so you don’t have to worry any longer about how to do LPAD(), RPAD(), and REPEAT().