Derived Column expression assistance

Question

Derived Column expression assistance

chrismgarvey

SSC Eights!

Points: 834
More actions
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

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

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

Erik Kutzler SSCarpal Tunnel Points: 4287 More actions · Answer 1

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)".

Phil Parkin SSC Guru Points: 246724 More actions · Answer 2

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))

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

chrismgarvey SSC Eights! Points: 834 More actions · Answer 3

Hi Erik,

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

Chris