Only get certain characters from a field

[Anonymous]

Hi

I have a field with values shown below. Is there a way to write a script to only get the ID after the last '_' sign as shown in the ID column?

Value	ID
MCS_NY_2523_B12	B12
INC_NJ_2522_A1	A1
RETDS_BY_LO__TIM_S130	S130

[jschrader]

=subfield(Value, '_', 4)

[antoniotiman]

SubField(Value,'_',-1)

[Digvijay_Singh]

Many ways to do it, as of now I could get - Get the index of last '_', minus it from total length and extract it from right..

=right('INC_NJ_2522_A1',len('INC_NJ_2522_A1')-index('INC_NJ_2522_A1','_',-1) )

[maximiliano_vel]

LOAD Value

     Right(Value, LEN(Value) - FindOneOf(Value,'_',SubStringCount(Value, '_'))) as ID

FROM <DataSource>

[jschrader · Accepted solution]

You do need to use index if you have varying number of _. I did not see that the first time. Nicole is right.

=right(Value,len(Value)-Index(Value,'_',-1))

I need to stop getting excited because I know the answer and read the full post first

[sasiparupudi1]

load Value,SubField(Value,'_',SubStringCount(Value,'_')+1) as ID

inline

[

Value

MCS_NY_2523_B12

INC_NJ_2522_A1

RETDS_BY_LO__TIM_S130

];

[#8]

Try below:

subfield(FieldName ,'_' ,-1)