Post reply

Derived Column expression assistance

  • January 8, 2010 at 8:08 am

    #208720

    Hi All,

    I have a column with the following value:

    FILE END ROWCOU?NT=83;

    I wish to only return the '83' part of this column and am trying to perform this transformation using a derived column. Is this the right approach? Can anyone help me achieve this?

    Many Thanks in advance.

    Chris

  • January 8, 2010 at 12:51 pm

    #1101147

    You can do this with a derived column, assuming the value you want to capture is always preceded with an equal sign and followed by a semicolon.

    Here's the syntax that will get the value and convert it to an integer. Just change "ColumnA" to the name of your column:

    (DT_I4)SUBSTRING(ColumnA,FINDSTRING(ColumnA,"=",1) + 1,FINDSTRING(ColumnA,";",1) - FINDSTRING(ColumnA,"=",1) - 1)

    If you don't want to convert it to an integer, remove "(DT_I4)".

  • January 8, 2010 at 11:56 pm

    #1101315

    Or you can reverse the string and parse from the right - useful if there is a possibility of multiple '=' or ';' in your input strings.

    REVERSE(SUBSTRING(REVERSE([Column 0]),2,FINDSTRING(REVERSE([Column 0]),"=",1) - 2))

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • January 11, 2010 at 7:23 am

    #1101772

    Hi Erik,

    This is just what I was looking for. Many Thanks for your suggestion.

    Chris

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply