添加一个.每3位数字/填充在SELECT中为0 [英] Add a . every 3rd digit/pad with 0 in SELECT

问题描述

在MS Access 2007项目中，我有一个包含以下记录源的报告:

In an MS Access 2007 project, I have a report with the following Record Source:

SELECT SomeTable.SomeCol FROM SomeTable;

SomeCol 应该为1、2、3、6或9位数字.

SomeCol should be either 1, 2, 3, 6, or 9 digits long.

让我们以3、6和9个第一.假设它是123-应该保留为123.如果它是123456，则应将其更改为123.456.如果它是123456789，则应将其更改为123.456.789.如果是1或65，则应分别将其更改为001和065.

Let's take the 3, 6 & 9 first. Say it is 123 - this should remain as 123. If it's 123456, this should be changed to 123.456. If it's 123456789, this should be changed to 123.456.789. If it's 1, or 65, this should be changed to 001 and 065 respectively.

如何修改我的SELECT以执行这些更改?如果长度不是1、2、3、6或9，则字符串应保持不变.

How can I modify my SELECT to perform these changes? If the length is not 1, 2, 3, 6, or 9, the string should remain unmodified.

谢谢！

推荐答案

我找到了一个解决方案，尽管它不是很漂亮.

I found a solution, although it's not very pretty.

实际上，SomeTable.SomeCol是一个很长的字符串，因此在我的系统上看起来更糟. SELECT是更大的查询的一部分，具有多个JOIN，因此需要SomeTable.我觉得这是最好的解决方案:

In reality, SomeTable.SomeCol is a very long string, so it looks much worse on my system. The SELECT is part of a much larger query, with multiple JOIN's, so SomeTable is required. I have a feeling this is the best solution:

SELECT switch(
    len(SomeTable.SomeCol) < 3,
        format(SomeTable.SomeCol, '000'),
    len(SomeTable.SomeCol) = 6,
        format(SomeTable.SomeCol, '000\.000'),
    len(SomeTable.SomeCol) = 9,
        format(SomeTable.SomeCol, '000\.000\.000'),
    true,
        SomeTable.SomeCol
) as SomeCol
FROM SomeTable;

这篇关于添加一个.每3位数字/填充在SELECT中为0的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！