sqlite is missing several basic database features compared to typical SQL servers and unfortunately padding is one of them. For a most situations though, you can use a simple hack to get the same functionality. This trick uses a combination of concatenation and substr:
-- 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
The statement is fairly self-explanatory, but in case it doesn’t make sense we’re simply adding a big long string of characters to the original value and then truncating it down to the desired length. The string used for concatenation has to be at least the same length that you are padding – 10 characters is used in this example (’0000000000′).
In most cases this workaround produces the same results as lpad/rpad except in the case where the length of your original value is greater than the length that you are padding. In which case the original value would get truncated. Usually when you are padding you know what the maximum length of the column anyway.
If you know of a more efficient technique, please post a comment.