How to emulate LPAD/RPAD with SQLite

Question

I'm curious about how to emulate RPAD and LPAD functions for SQLite, formally, in the most general way. The goal is to be able to do

LPAD(column, character, repeat)
RPAD(column, character, repeat)

For non-constant table columns column, character, repeat. If character and repeat were known constants, then this would be a good, viable solution:

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

But what if the above should be executed like this:

SELECT LPAD(t.column, t.character, t.repeat) FROM t
SELECT LPAD(t.column, some_function(), some_other_function()) FROM t
SELECT LPAD(t.column, :some_bind_variable, :some_other_bind_variable) FROM t

How could this LPAD function be generally emulated? I'm lost with the possibilities:

• http://www.sqlite.org/lang_corefunc.html

A related question:

• How to emulate REPEAT() in SQLite

Answer 1

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

Answer 2

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.