The
||operator is "concatenate" - it joins together the two strings of its operands.
From http://www.sqlite.org/lang_expr.html
For padding, the seemingly-cheater way I've used is to start with your target string, say '0000', concatenate '0000423', then substr(result, -4, 4) for '0423'.
Update: Looks like there is no native implementation of "lpad" or "rpad" in SQLite, but you can follow along (basically what I proposed) here: 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
Here's how it looks:
SELECT col1 || '-' || substr('00'||col2, -2, 2) || '-' || substr('0000'||col3, -4, 4)
it yields
"A-01-0001"
"A-01-0002"
"A-12-0002"
"C-13-0002"
"B-11-0002"