SQLite格式化数字前导零的函数是什么？

Question

我有一个 SQLite 数据库需要包含产品。这些产品有一个固定格式的复合产品编号，由类别、组和产品编号（cccccc.gg.ppp）组成。每次插入新产品时，新的产品号应该使用类别和组号构建，接着是该组中最高的产品号加一。

为此，我考虑将所有三个数字存储在单独的字段中，并使用视图动态组装产品号。为了让它生效，我需要添加前导零，因为产品号的格式是固定的。

然而，我找不到内置的 SQLite 函数可以让我做到这一点… :-(

我不想编写自定义函数，因为我们可能需要与其他开发人员共享数据库，让他们从中读取数据；而且我不知道他们将使用什么平台或语言。

能做到吗？还是我们必须找到另一个解决方案？

- Martijn

---

除了 @9000 直接回答你的问题之外，还要考虑类别、组和产品编号是否应该实际存储为文本。 - Mike Sherrill 'Cat Recall'

为什么它们应该被存储为文本而不是数字？（特别是产品编号，因为我需要获取其最大值+1以获得下一个产品编号）。 - Martijn

文本保留前导零。当您的要求是存储“000123”时，存储“000123”确实有其可取之处。 - Mike Sherrill 'Cat Recall'

那不是问题。我需要获取下一个值；这意味着将其作为数字可用，并将其转换为带有零前缀的字符串。它如何存储完全是另一回事。 - Martijn

您可以将字符串转换为数字进行算术运算。请访问http://www.sqlite.org/lang_expr.html#castexpr。 - Mike Sherrill 'Cat Recall'

Answer 1

使用字符串拼接和 substr 函数可以轻松实现此操作。简而言之，这就是你想要的：

select substr('0000000000'||'1234', -10, 10)

下面是一个解释。

首先，要知道SQLite的字符串连接运算符是||。

我们将从一个与要返回的值一样长的字符串开始。例如，如果您想返回始终为10个字符长度的数字，并在其长度不足时用左侧的0进行填充，则需要以十个0的字符串开头。然后，我们将连接您想要返回的数字。在我们的示例中，这是“1234”。到目前为止，我们有了如下部分：'0000000000'||'1234'

然后，将整个字符串传递到substr函数中。根据文档，substr函数的工作原理如下：

substr（X，Y，Z）函数返回输入字符串X的子字符串，该子字符串从第Y个字符开始，并且长度为Z个字符。…如果Y为负数，则通过从右边而不是左边计数来找到子字符串的第一个字符。

因此，如果您希望字符串长度为10个字符，请将-10作为Y参数传递（从右侧开始计数，倒数第10个字符），并将10作为Z参数传递（总共取10个字符）。

- Steve Moore

Answer 2

自3.8.3 (2014) 起，您可以使用 printf 如下：

SELECT printf('%04d', product_number) AS product_number FROM table;

将数字 4 替换为您需要的位数。这将返回产品编号 1 的 0001。

- shao.lo

---

我想补充一点，在SQLite中使用双引号表示字符串字面量是不被鼓励和遗憾的（请参见https://sqlite.org/quirks.html#double_quoted_string_literals_are_accepted）。下面的表达式可能更好：`printf('%04d', product_number)`。不过，回答很好。 - Manngo

谢谢@Manngo。我已经更新了它，使用单引号。 - shao.lo

Answer 3

我知道这是一个旧问题，但以下方法更简单：

--  zero-pad to 4 digits:
select substr('0000'||3,-4);

substr如果选择到字符串结尾，则不需要指定长度。

- Manngo

Answer 4

select substr('0000000000'||'1234', length('0000000000'||'1234')-9, 10) 可以使用；将 '1234' 替换为您的产品号码，长度不能超过10个字符。

- 9000