SQLite 函数用前导零格式化数字?

2022-01-17 00:00:00 numbers formatting leading-zero sqlite

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

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

但是，我似乎找不到可以让我这样做的内置 SQLite 函数... :-(

我宁愿不编写自定义函数，因为我们可能必须与其他开发人员共享数据库，以便他们从中读取数据；而且我不知道他们将使用什么平台或语言.

这可以吗?还是我们必须找到不同的解决方案?

解决方案

这可以通过使用字符串连接和 substr 函数来完成.长话短说，这就是你想要的:

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

下面是解释.

首先要知道SQLite的字符串连接操作符是||.

我们将从一个与我们想要返回的值一样长的字符串开始.例如，如果您想返回一个长度始终为 10 个字符的数字，并且如果它更短，则用 0 填充它，那么从一个包含 10 个字符的字符串开始 0s.为此，我们将连接您要返回的数字.在我们的示例中，即为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).

相关文章