string 如何在sqlite中使用填充连接字符串
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6134415/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to concatenate strings with padding in sqlite
提问by Akshara
I have three columns in an sqlite table:
我在一个 sqlite 表中有三列：
Column1 Column2 Column3
A 1 1
A 1 2
A 12 2
C 13 2
B 11 2
I need to select Column1-Column2-Column3(e.g. A-01-0001). I want to pad each column with a -
我需要选择Column1-Column2-Column3（例如A-01-0001）。我想用一个填充每一列-
I am a beginner with regards to SQLite, any help would be appreciated
我是 SQLite 的初学者，任何帮助将不胜感激
回答by tofutim
The
||operator is "concatenate" - it joins together the two strings of its operands.
该
||运营商是“连击” -它加入其操作数的两个字符串。
From http://www.sqlite.org/lang_expr.html
来自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'.
对于填充，我使用的看似骗子的方法是从目标字符串开始，比如“0000”，连接“0000423”，然后 substr(result, -4, 4) 表示“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/
更新：看起来在 SQLite 中没有“lpad”或“rpad”的本地实现，但你可以在这里遵循（基本上是我提出的）：http: //verysimple.com/2010/01/12/sqlite-lpad -rpad-功能/
-- 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"
回答by ybungalobill
SQLite has a printffunctionwhich does exactly that:
SQLite 有一个printf函数可以做到这一点：
SELECT printf('%s-%.2d-%.4d', col1, col2, col3) FROM mytable
回答by Madan Sapkota
Just one more line for @tofutim answer ... if you want custom field name for concatenated row ...
@tofutim 的答案只需要多一行...如果您想要连接行的自定义字段名称...
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!
在SQLite 3.8.8.3上测试，谢谢！
