SSIS中如何仅移除xx-xx-xxxx_格式前缀且不影响其他列值

原有方案的问题 #

你之前写的两类逻辑都存在缺陷：

• SSMS中使用的反转截取SQL没有做格式前置校验，只要字符串内包含下划线就会执行截取，会误改11614491_R、11283627_P这类带下划线但不符合目标前缀格式的值
• 后续写的SSIS表达式存在三个硬错误：
  • FINDSTRING函数不支持通配符匹配，传入的__-__-____[_]是正则语法，函数只会把它作为普通字符串查找，永远无法匹配到目标前缀
  • 字段名拼写错误，SUBSTRING中写的IIDENTIFIER多了一个首字母I，运行会触发字段不存在的报错
  • 没有做长度校验，如果遇到长度不足11位、刚好匹配到错误位置字符的情况，会出现截取异常

正确的SSIS派生列表达式 #

目标格式的校验逻辑不需要用正则，直接按固定位置判断字符即可：符合要求的值前11位固定为xx-xx-xxxx_结构，即第3位、第6位为-，第11位为_，且字符串总长度大于11（保证下划线后有内容）。
直接在派生列组件中使用以下表达式即可：

(LEN(IDENTIFIER) > 11 && SUBSTRING(IDENTIFIER,3,1) == "-" && SUBSTRING(IDENTIFIER,6,1) == "-" && SUBSTRING(IDENTIFIER,11,1) == "_") 
? SUBSTRING(IDENTIFIER,12,LEN(IDENTIFIER) - 11) 
: IDENTIFIER

如果需要更严谨的校验（比如要求前缀中除横杠外的字符均为数字），可以在判断条件中追加对应规则，例如新增ISDIGIT(SUBSTRING(IDENTIFIER,1,2))判断前两位为数字，按需扩展即可。

处理效果测试 #

用给出的样例值测试，结果完全符合需求：

• 输入31-03-2022_13636075 → 输出13636075
• 输入01-04-2022_13650262 → 输出13650262
• 输入20220099001 → 保留原值20220099001
• 输入11614491_R → 保留原值11614491_R
• 输入10781198 → 保留原值10781198
• 输入-1 → 保留原值-1
• 输入00000000000 → 保留原值00000000000

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