sql - 如何用 SQLite 模拟 LPAD/RPAD

Question

我很好奇如何以最通用的方式正式地为 SQLite 模拟 RPAD 和 LPAD 函数。目标是能够做到

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

对于非常量表的列column，character，repeat。如果 character 和 repeat 是已知常量，那么这将是一个好的、可行的解决方案:

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

但是如果上面的代码应该像这样执行呢:

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

这个LPAD 函数如何被普遍模拟？我迷失了可能性:

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

相关问题:

• How to emulate REPEAT() in SQLite

Answer 1

复制自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