How to Emulate Lpad/Rpad with SQLite

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)
    );

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

Oracle SQL - Missing Right parenthesis using LPAD and RPAD

If you look at one of these lines that are causing the issue:

RPAD ((LPAD (c.total_acct), 6, 0), 6)

you should see that the call to LPAD() does not have enough parameters. Maybe you mean this?

RPAD( LPAD( c.total_acct, 6, 0 ), 6 )

The same is the case with the other line causing the issue. The error message is a bit confusing - the problem is actually too many parentheses.

Hope this helps.

Get range starting from a specific record / item-keyed pagination

SQLite supports tuple comparison, so you can do:

SELECT * 
FROM Entries
WHERE (s1, s2, s3) < (29, 30, 30)
ORDER BY s1, s2, s3
LIMIT 5;

---

Related Topics