在sql中格式化wbs列

Question

如何分割，然后用SQL格式化wbs列的前缀为零？

示例: 1.2.15至1.002.015

样本WBS栏内容：

 - 1.1
 - 1.1.1
 - 1.1.2
 - 1.1.3
 - 1.2

Answer 1

这并不是地球上最美丽的代码，但它确实起到了作用：

DECLARE @STR VARCHAR(100) = '1.2.15'
DECLARE @FORMAT VARCHAR(10) = '000'

DECLARE @P1 VARCHAR(10)
DECLARE @P2 VARCHAR(10)
DECLARE @P3 VARCHAR(10)
DECLARE @P4 VARCHAR(10)

DECLARE @PARTS INT = 1 + LEN(@STR) - LEN(REPLACE(@STR, '.', ''))

SELECT @P1 = PARSENAME(@STR, @PARTS)
SELECT @P2 = PARSENAME(@STR, @PARTS - 1) 
SELECT @P3 = PARSENAME(@STR, @PARTS - 2) 
SELECT @P4 = PARSENAME(@STR, @PARTS - 3)

SELECT @P2 = FORMAT(CAST(@P2 AS INT), @FORMAT)
SELECT @P3 = FORMAT(CAST(@P3 AS INT), @FORMAT)
SELECT @P4 = FORMAT(CAST(@P4 AS INT), @FORMAT)

SELECT ISNULL(@P1, '') + ISNULL('.' + @P2, '') + ISNULL('.' + @P3, '') + ISNULL('.' + @P4, '')
-- Output is 1.002.015

关键是使用PARSENAME函数。它的目的是解析一个完全限定的SQL对象名称，但是这里我们将将它用于WBS。我首先找到点数来知道它有多少个部分，因为这个函数的第二个参数是从末尾开始计数的部分。这样我就可以在@P1中获得主版本，在@P2中获得下一个版本，等等。

然后，我做了一个添加前导零的技巧，最后我只是将数字连接起来，避免了如果其中一个为NULL的字符串为空。

限制：

1. 只有4部分(不能用于1.2.15.6.3)
2. 必须是有效的，没有空格和没有。在最后

Answer 2

Declare @wbs nvarchar(MAX)
Set @wbs ='1.1.12.123.1234.12345.123456.1234567.123456789.1234567890'

DECLARE @XML AS XML
DECLARE @delimiter AS CHAR(1) = '.' -- wbs character seperator
SET @XML = CAST(('<WBS>'+REPLACE(@wbs, @delimiter,'</WBS><WBS>')+'</WBS>') AS XML)

DECLARE @tempTable TABLE (ID INT IDENTITY(1, 1) primary key, WBS INT)
INSERT INTO @tempTable SELECT N.value('.', 'INT') AS ID FROM @XML.nodes('WBS') AS T(N)

DECLARE @formattedWbs  varchar(MAX) = '',
        @wbsSplit  INT,
        @id INT,
        @skip bit = 1;

WHILE EXISTS (SELECT * FROM  @tempTable)
BEGIN
    SELECT TOP 1 @wbsSplit = WBS, @id = ID FROM @tempTable;

    IF @skip = 1
        BEGIN
            SET @formattedWbs +=  @wbsSplit;
            SET @skip = 0;
        END
    ELSE
        BEGIN
            SET @formattedWbs += '.' + FORMAT(@wbsSplit,'000#');
        END

    DELETE @tempTable Where ID = @id;
END

PRINT @formattedWbs;

Answer 3

创建函数GetFormattedWBS (@wbs VARCHAR(MAX))返回varchar(MAX)开始设置@wbs +=‘。

DECLARE @formattedwbs NVARCHAR(MAX);
SET @formattedwbs = '';

WHILE (CHARINDEX('.', @wbs) > 0)
BEGIN
    DECLARE @wbsnode INT;
    SET @wbsnode = SUBSTRING(@wbs, 1, CHARINDEX('.', @wbs) - 1);
    SET @formattedwbs +=  FORMAT(@wbsnode,'00000#') + '.';
    SET @wbs = SUBSTRING(@wbs, CHARINDEX('.', @wbs) + 1, len(@wbs));
END

SET @formattedwbs = SUBSTRING(@formattedWbs, 1, (len(@formattedWbs) - 1));  

RETURN @formattedwbs

结束