January 8, 2010 at 8:08 am
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
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
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
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