SQL拆分字母与数值后续问题：负数处理及SSIS派生列转换

一、修复SQL中的负号丢失问题 #

你的原查询问题出在没有考虑括号包裹的负数，以及没有把负号纳入数值的匹配范围里。我们可以先清理掉括号，再调整匹配逻辑，确保负号被包含在数值提取中：

SELECT 
    [Quantity],
    -- 提取单位：清理括号后，截取第一个非数值字符（-、0-9、.除外）之后的部分，再去除空格
    LTRIM(SUBSTRING(CLEANED_QUANTITY, PATINDEX('%[^-0-9.]%', CLEANED_QUANTITY), LEN(CLEANED_QUANTITY))) AS unit,
    -- 提取数值：清理括号和逗号后，截取到第一个非数值字符之前的部分
    LEFT(REPLACE(CLEANED_QUANTITY, ',', ''), PATINDEX('%[^-0-9.]%', REPLACE(CLEANED_QUANTITY, ',', '') + 'x') - 1) AS num
FROM (
    SELECT 
        [Quantity],
        -- 先去掉前后括号，得到干净的字符串
        REPLACE(REPLACE([Quantity], '(', ''), ')', '') AS CLEANED_QUANTITY
    FROM [OPI].[dbo].[MRRInvoices]
    WHERE DocumentNum IS NOT NULL
) t

这个查询会正确处理(-1.00 GB)这类带括号的负数：

• 清理后字符串为-1.00 GB
• num会提取为-1.00，完整保留负号
• unit会提取为GB

二、SSIS中实现该逻辑的两种方案 #

SSIS的派生列组件没有直接对应PATINDEX的函数，但可以通过两种方式实现需求：

方案1：用派生列函数组合（适用于格式固定的场景）

如果你的数值和单位始终用空格分隔，可以用TOKEN函数快速拆分：

1. 先创建派生列CLEANED_QUANTITY，表达式：

   REPLACE(REPLACE(Quantity, "(", ""), ")", "")
   

2. 提取数值num，表达式：

   REPLACE(TOKEN(CLEANED_QUANTITY, " ", 1), ",", "")
   

3. 提取单位unit，表达式：

   TOKEN(CLEANED_QUANTITY, " ", 2)
   

这个方法简单高效，但只适用于数值和单位严格用空格分隔的情况。

方案2：用脚本组件（灵活适配各种格式）

如果你的数据格式不固定，推荐用脚本组件结合正则表达式处理，这是最可靠的方式：

1. 在SSIS数据流中添加脚本组件，选择“转换”类型
2. 输入列选择Quantity，输出列添加num（字符串类型）和unit（字符串类型）
3. 编辑脚本，替换为以下C#代码：

   using System.Text.RegularExpressions;
   
   public override void Input0_ProcessInputRow(Input0Buffer Row)
   {
       if (!Row.Quantity_IsNull && !string.IsNullOrEmpty(Row.Quantity))
       {
           // 清理括号
           string cleanedText = Row.Quantity.Replace("(", "").Replace(")", "");
           // 正则匹配：第一组是带负号/逗号的数值，第二组是单位
           Match matchResult = Regex.Match(cleanedText, @"^(-?[\d,.]+)\s*(.+)$");
           
           if (matchResult.Success)
           {
               // 去掉数值中的逗号，赋值给num
               Row.num = matchResult.Groups[1].Value.Replace(",", "");
               // 去除单位前后的空格，赋值给unit
               Row.unit = matchResult.Groups[2].Value.Trim();
           }
           else
           {
               // 格式不匹配时标记为null
               Row.num_IsNull = true;
               Row.unit_IsNull = true;
           }
       }
       else
       {
           Row.num_IsNull = true;
           Row.unit_IsNull = true;
       }
   }
   

这个方法可以处理各种格式的输入，包括带负号、千分位逗号、任意空格分隔的情况。

内容的提问来源于stack exchange，提问作者Yaeli Hajbi