CODEWITHSUNDEEP
sql-serverssisetl
Bokbob
Bokbob

Reputation: 89

how to split the value with characters and numbers in SSIS

i have values like ABC1234, AB12, CDFHY1234 etc. in the excel am reading from in SSIS and i would need to split them separating letters and numbers. as you can see we cant predict the number letters those can be in the value.

Please help me to figure out a way to split them separately in SSIS.

Thanks for your help.

Upvotes: 1

Views: 668

Answers (1)

Hadi
Hadi

Reputation: 37368

First, you have to add a Script Component in your DataFlowTask and add 2 output Columns in it. and mark your Column as an Input Column.

Second, You Have to use a script to split this String Value.

Inside your script Declare these Functions

Private Shared Function GetNum(ByVal value As String) As Integer
    Dim mytext As String = String.Empty
    Dim myChars() As Char = Value.ToCharArray()
    For Each ch As Char In myChars
         If Char.IsDigit(ch) Then
              myText &= ch
         End If
    Next
    Return Cint(myText)
End Function


Private Shared Function GetText(ByVal value As String) As String
    Dim mytext As String = String.Empty
    Dim myChars() As Char = Value.ToCharArray()
    For Each ch As Char In myChars
         If Char.IsLetter(ch) Then
              myText &= ch
         End If
    Next
    Return myText
End Function

Consider that your input Column is inCol and your Output Columns are outNum and outText

If Row.inCol_IsNull = False Then
    Row.outText = GetText(Row.inCol)
    Row.outNum = GetNum(Row.inCol)
Else
    Row.outText_IsNull = True
    Row.outNum_IsNull = True
End If

Upvotes: 1

Related Questions