SQLite函数来格式化数字与前导零？ [英] SQLite function to format numbers with leading zeroes?

问题描述

我有一个需要包含产品的SQLite数据库。这些产品具有固定格式的复合产品编号，由类别，组和产品编号（cccccc.gg.ppp）组成。每次插入新产品时，应使用类别和组号建立新产品编号，然后在该组中最高的产品编号加1。为此，我正在考虑将所有三个数字存储在不同的字段中，并使用视图动态组装产品编号。对于这个工作，我需要添加前导零，因为产品编号的格式是固定的。

但是，我似乎无法找到一个内置的SQLite函数，让我这样做...： - （$ / b
$ b

我宁愿不写一个自定义函数，因为我们可能必须与其他开发人员共享数据库，让他们读取数据;我不知道他们将使用什么平台或语言。

这可以做到吗？或者我们必须找到一个不同的解决方案？

解决方案

这可以使用字符串连接和 substr 函数。长话短说，这里是你想要的：

  select substr（'0000000000'|| '1234'，-10，10）
  

以下是一个解释。

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

由tak开始一个与我们想要返回的值一样长的字符串。例如，如果要返回一个长度始终为10个字符的数字，并且如果它的长度较短，则使用 0 s填充剩余的数字，然后从十个字符开始 0 秒。为此，我们将连接你想要返回的数字。在我们的例子中，这是'1234'。到目前为止，我们有这样的部分：'0000000000'||'1234'

然后，将整个事物传递给 substr 函数。 根据文档，以下是 substr 函数的工作原理：

substr（X，Y，Z）函数返回输入字符串X的一个子字符串，该字符串以第Y个字符开头，长度为Z个字符。 ...如果Y是负数，则子字符串的第一个字符是从右边而不是左边开始计算的。

所以如果你希望字符串长度为10个字符，传递-10作为Y参数（从右边开始计数，返回10个字符）并传递10作为Z参数（总共需要10个字符）。

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?

解决方案

This can be accomplished with a little bit of magic using string concatenation and the substr function. Long story short, here's what you want:

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

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 ten 0s. 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 substr function. According to the documentation, here's how the substr function works:

The substr(X,Y,Z) function returns a substring of input string X that begins with the Y-th character and which is Z characters long. ... If Y is negative then the first character of the substring is found by counting from the right rather than the left.

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).

这篇关于SQLite函数来格式化数字与前导零？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！