i have an string like this format in my case i need to extract only the characters from string case

1)12AB
2)SD12
3)1WE4

output

1)AB
2)SD
3)WE

i need to extract only the characters i am using this expresion in the dervied column in SSIS package

SUBSTRING(MediaIDCode,1,2)

but this expresssion works for only this condition

1)12AB

i need to get an expressioin that works for all the conditions above i have tried using REPLACE ( '' , '1', '') but it becomes an big expression

any help would be great

Thanks

Prince

• ssis

The SUBSTRING function code you've posted will extract characters from a string without considering their content.

Have a look at using Regular Expressions to filter out unwanted characters:

http://consultingblogs.emc.com/jamiethomson/archive/2005/07/04/SSIS-Nugget_3A00_-The-script-component-and-regular-expressions.aspx

You could also use a synchronous script transformation to filter characters this way:

    Dim NewMediaIDCode As String = ""

    For Each c As Char In Row.MediaIDCode
        If Not Char.IsDigit(c) Then
            NewMediaIDCode += c
        End If
    Next

    Row.MediaIDCode = NewMediaIDCode

I've used the IsDigit method here, but there's plenty of other methods to choose from.