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

Here's more nastiness for you:

X = padToLength
Y = padString
Z = expression

RPAD (for LPAD, Z is concatenated after instead):

select 
    Z || 
    substr(
        replace(
            replace(
                substr(
                    quote(zeroblob(((X - length(Z) - 1 + length(Y)) / length(Y) + 1) / 2)), 
                    3
                ), 
                "'", 
                ""
            ), 
            "0", 
            Y
        ),
        1,
        (X - length(Z))
    )

Examples:

sqlite> select "foo" || replace(replace(substr(quote(zeroblob((2 + 1) / 2)), 3, (2 - length("foo"))), "'", ""), "0", "W");
foo
sqlite> select "foo" || replace(replace(substr(quote(zeroblob((7 + 1) / 2)), 3, (7 - length("foo"))), "'", ""), "0", "W");
fooWWWW

Sqlite is meant to be quite lightweight, so I have to disagree somewhat with your comment about being "surprised" by the lack of functionality. However, I agree that there should be a simpler way to do padding, if only because the trim functions exist.

You could also PRINTF for the cases of 0 and space left padding:

sqlite> SELECT PRINTF('%02d',5);
05
sqlite> SELECT PRINTF('%2d',5);
 5
sqlite> SELECT PRINTF('%04d%02d',25,5);
002505
sqlite> 

Starting from SQLite 3.38.0 (February 2022, introduced in this commit) printf becomes an alias for the FORMAT function for greater compatibility with other DBMSs. The function is documented at: https://www.sqlite.org/lang_corefunc.html#format FORMAT is not however standardized. and e.g. PostgreSQL 14 FORMAT does not recognize %d, only %s.

Copied from http://verysimple.com/2010/01/12/sqlite-lpad-rpad-function/

-- the statement below is almost the same as
-- select lpad(mycolumn,'0',10) from mytable

select substr('0000000000' || mycolumn, -10, 10) from mytable

-- the statement below is almost the same as
-- select rpad(mycolumn,'0',10) from mytable

select substr(mycolumn || '0000000000', 1, 10) from mytable