SQLite 函数用前导零格式化数字？
我有一个需要包含产品的 SQLite 数据库。这些产品具有固定格式的复合产品编号，由类别、组和产品编号 (cccccc.gg.ppp) 组成。每次插入新产品时，应使用类别和组编号来构建新产品编号，然后是该组中的最高产品编号加 1。
为此，我正在考虑将所有三个数字存储在不同的字段中，并使用视图动态组合产品编号。为此，我需要添加前导零，因为产品编号的格式是固定的。
但是，我似乎找不到一个内置的 SQLite 函数可以让我这样做...:-(
我不想编写自定义函数，因为我们可能必须与其他开发人员共享数据库，因为他们从中读取数据；我不知道他们将使用什么平台或语言来
完成此操作？还是我们必须找到不同的解决方案？
I’ve got an SQLite database that will need to contain products. Those products have a fixed-format composite product number, consisting of category, group and product number (cccccc.gg.ppp). Every time a new product is inserted, the new product number should be built using the category and group numbers, followed by the highest product number in that group incremented with one.
To that end, I was thinking of storing all three numbers in separate fields, and use a view to dynamically assemble the product number. For that to work, I would need to add leading zeroes, since the product number’s format is fixed.
However, I can’t seem to find a built-in SQLite function which would let me do this... :-(
I’d rather not write a custom function, since we might have to share the database with other developers, for them to read data from; and I have no idea what platform or language they’re going to use.
Can this be done? Or will we have to find a different solution?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
这可以通过使用字符串连接和 substr 函数来实现。长话短说，这就是您想要的：
以下是解释。
首先，知道SQLite的字符串连接运算符是
||。我们首先获取一个与我们想要返回的值一样长的字符串。例如，如果您想要返回一个始终为 10 个字符长的数字，并在其较短的情况下用
0进行填充，则以十个0的字符串开始代码>s.为此，我们将连接您要返回的数字。在我们的示例中，即“1234”。到目前为止，我们的部分如下所示：'0000000000'||'1234'然后，将整个内容传递到
substr函数中。 根据文档，以下是substr函数的工作原理：因此，如果您希望字符串长度为 10 个字符，请传递 -10 作为 Y 参数（从右侧开始计数，向后 10 个字符），并传递 10 作为 Z 参数（总共 10 个字符）。
This can be accomplished with a little bit of magic using string concatenation and the
substrfunction. Long story short, here's what you want:An explanation follows.
First, know that SQLite's string concatenation operator is
||.We'll start by taking a string that is as long as the value we want to return. For example, if you want to return a number that is always 10 characters long and have it be left padded with
0s if it is shorter, then start with a string of ten0s. To that, we will concatenate the number you want to return. In our example, that is '1234'. So far, we have the part that looks like this:'0000000000'||'1234'Then, pass that whole thing into the
substrfunction. According to the documentation, here's how thesubstrfunction works:So if you want the string to be 10 characters long, pass -10 as the Y parameter (to start counting from the right, 10 characters back) and pass 10 as the Z parameter (to take 10 characters total).
从 3.8.3 (2014) 开始，您可以使用 printf 为：
将 4 更改为任意数量您需要的数字。这将为产品编号 1 返回 0001。
Since 3.8.3 (2014) You can use printf as:
Change the 4 to however many digits you need. This would return 0001 for product_number 1.
我知道这是一个老问题，但以下更简单：
'0000' || 3将零连接到数字的开头。 SQLite 不介意连接数字，因为它会为您将它们转换为字符串。substr(…, -4)提取字符串的一部分。-4表示从末尾开始四个字符。通常，您可能有第三个参数，但将其省略会选择到最后。I know this is an old question, but the following is simpler:
'0000' || 3concatenates zeroes to the beginning of the number. SQLite doesn’t mind concatenating numbers as it will convert them to strings for you.substr(… , -4)extracts part of the string. The-4means four characters from the end. Normally, you might have third parameter, but leaving it out selects to the end.select substr('0000000000'||'1234', length('0000000000'||'1234')-9, 10)有效；将'1234'替换为您的产品编号，任意长度 <= 10。select substr('0000000000'||'1234', length('0000000000'||'1234')-9, 10)works; substitute'1234'with your product number, any length <= 10.