How to concatenate strings with padding in sqlite
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 mytableselect substr('0000000000' || mycolumn, -10, 10) from mytable-- the statement below is almost the same as-- select rpad(mycolumn,'0',10) from mytableselect 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"
SQLite has a printf
function which does exactly that:
SELECT printf('%s-%.2d-%.4d', col1, col2, col3) FROM mytable
Just one more line for @tofutim answer ... if you want custom field name for concatenated row ...
SELECT ( col1 || '-' || SUBSTR('00' || col2, -2, 2) | '-' || SUBSTR('0000' || col3, -4, 4) ) AS my_column FROM mytable;
Tested on SQLite 3.8.8.3, Thanks!