SSIS 派生列(如果然后...其他)

问题描述：

有没有办法在 SSIS 派生列中复制以下表达式?

is there a way of replicating the below expression in SSIS Derived Column?

SELECT CASE
           WHEN LEN(column1) > 8
               THEN
                   column1
               ELSE
                   REPLICATE('0', 18 - LEN(column1)) + column1
           END AS column1
FROM myTable

如果值的长度小于 8 个字符，我想用 0 填充 column1 的值

I want to pad the value of column1 with 0 if the lenght of the value is less than 8 character

答

SSIS 表达式语言支持三元运算符 ?:

The SSIS expression language supports the ternary operator ? :

(LEN([column1]) > 8) ? column1 : replicate("0", (18 - LEN([column1]))) + [column1]

该表达式应该起作用，但它不起作用，因为 REPLICATE 调用将返回元数据，说明它是 4k nvarchar 字符(限制).如果您对让它以这种方式工作感到僵硬，请发表评论，我将在与 column1

That expression ought to work but it doesn't because the REPLICATE call is going to provide metadata back stating it's 4k nvarchar characters (the limit). If you're deadset on getting it to work that way, comment and I'll hack the expression to size the output of replicate before concatenating with column1

一种更简单的方法是始终在表达式中添加 8 个前导零，然后从右侧将其切掉.

An easier approach is to just always add 8 leading zeros to the expression and then slice it off from the right.

RIGHT(REPLICATE("0",8) + column1,8)

第二个你可能需要 18，因为你的例子似乎使用了 18，但概念是一样的.

You might need 18 on the second as your example seemed to use 18 but the concept will be the same.

SSIS 派生列(如果然后...其他)

相关推荐