Extract values in a column based on a pattern in tmap
I have a requirement where my string looks like
Col1 :ABC_XYZ001_1234_ABCD.
Col2 : MNO
Now, if the Col1 String looks like ABC then, I need to extract XYZ001 alone from this string.
Else we need to take the Col 2 value directly.
Can someone help me to know how can I do that in tmap. I think this should be relatively simple. But I am relatively new to Talend; hence require your assistance. Thanks in advance.
But I don't want to take all the next 10 letters. Rather I need to take all the characters left before the second _(underscore) but after the first _(underscore)
For ex
if the string is ABC_MNOP111_1234_MNOQRS then take MNOP111
if the string is ABC_1234567890_0001_AABBCC then take 1234567890
if the string is ABC_1010_9999_1000 then take 1010 etc.,
well your scenario can be achieved by using Tmap and i would recommend you try out @dipanjan93 solution for the same,however there is slight change in the logic though...
in the TMap expression editor -
Well Considering that your use case start from ABC always then -
- check for the string whether it starts from ABC or not (row1.col1.contains('ABC')).
- check whether the string is delimited by underscore(just to be sure).
- use substring method with index of first underscore as the start index and for second underscore you have to be careful because if you try and use the same logic as the start index then it fails, thereby you must once use a nested function which is index of(row1.col1,substring(row1.col1,give negative index)).
Pls try the above logic... i havent given you the syntax in purpose that you try this out also there is a component which you can use as well which is tExtractDelimitedFields where you have give the string or the column in which the data is delimited with...
If you are unclear about anything, Pls do get back to the Community.
