topic Extract values in a column based on a pattern in tmap in Talend Studio

Extract values in a column based on a pattern in tmap

Anonymous — Sat, 16 Nov 2024 07:05:52 GMT

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.

Re: Extract values in a column based on a pattern in tmap

dipanjan93 — Fri, 07 Dec 2018 18:20:16 GMT

If the format is consistent, you could use the below code inside the tMap expression builder -

Col1.contains("ABC")?Col1.substring(4,10):Col2

Re: Extract values in a column based on a pattern in tmap

Anonymous — Fri, 07 Dec 2018 18:26:46 GMT

Thanks for the reply.

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.,

Re: Extract values in a column based on a pattern in tmap

ankit7359 — Sat, 08 Dec 2018 07:20:40 GMT

hi @Nightowl,

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.

Thanks,

Ankit