Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

Funky String Function Simulation in SQLite

DZone's Guide to

Funky String Function Simulation in SQLite

· Java Zone ·
Free Resource

Verify, standardize, and correct the Big 4 + more– name, email, phone and global addresses – try our Data Quality APIs now at Melissa Developer Portal!

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().

 

 

 

 

 

Developers! Quickly and easily gain access to the tools and information you need! Explore, test and combine our data quality APIs at Melissa Developer Portal – home to tools that save time and boost revenue. Our APIs verify, standardize, and correct the Big 4 + more – name, email, phone and global addresses – to ensure accurate delivery, prevent blacklisting and identify risks in real-time.

Topics:

Published at DZone with permission of

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}