sql - SSIS删除不需要的字符

Question

如何删除 SSIS 中文本之间不需要的字符

即我们有这样的数据

2134;#Adam Connor (aconnor),21987;#Tatanka Wabe (Twabe);# 

当它来自 sharepoint 时。我尝试了子字符串、替换等，但无法删除名称之间的数字。

我想要输出为

Adam Connor, Tatanka Kale

Answer 1

您可以使用正则表达式

注意:VB.NET 中的代码

您需要提取#和(之间的字符串

Dim mc As MatchCollection = Regex.Matches(strContent, "(?<=\#)(.*?)(?=\()", RegexOptions.Singleline)

然后你需要将它们连接起来并用逗号分隔

String.Join(",", mc.Cast(Of Match)().Select(Function(m) m.Value))

<小时/>

SSIS 版本 - 使用脚本组件

您可以使用脚本组件通过正则表达式来实现此目的:

假设Column0是输入列，outColumn是输出列

Imports System  
Imports System.Data  
Imports System.Math  
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper  
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper  
Imports System.Text.RegularExpressions

<Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute> _  
<CLSCompliant(False)> _  
Public Class ScriptMain  
    Inherits UserComponent  

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)  

        if Not Row.Column0_IsNull AndAlso _
           Not String.IsNullOrEmpty(Row.Column0.Trim) Then

            Dim strContent As String = Row.Column0

            Dim mc As MatchCollection = Regex.Matches(strContent, "(?<=\#)(.*?)(?=\()", RegexOptions.Singleline)

            Row.OutColumn = String.Join(",", mc.Cast(Of Match)().Select(Function(m) m.Value))

        Else 

            Row.OutColumn_IsNull = True

        End If

    End Sub  

End Class  

引用文献

• Regular Expression to find a string included between two characters while EXCLUDING the delimiters