topic Re: delete first 7 characters in column in tMap in Talend Studio

delete first 7 characters in column in tMap

Anonymous — Thu, 08 Mar 2018 09:20:13 GMT

Hi, I just started with Talend and am learning a lot. But I have an issue I can't resolve and haven't found a solution to yet.

I have a table with combined info. It consists of postal code and address and I want it split.Example: "6093 EA Heythuysen". It has to split in "6093 EA" and "Heythuysen".

Via tMap I have added the column twice to the right side.

In the first I did a stringhandling.LEFT on the first 7 positions and this works. I get the 6093EA.

On the other output I can't use a stringhandling.RIGHT because I have different values with different lengths.

I tried substring(7) but then I get a string index out of range error.

How can I get rid of the first 7 characters?

Thank you in advance from a novice Talend user.

Re: delete first 7 characters in column in tMap

manodwhb — Thu, 08 Mar 2018 09:30:32 GMT

in tmap first check the length of the string like below.

StringHandling.LEN(row3.test)>7 ? (row3.test).substring(7,StringHandling.LEN(row3.test)): row3.test

Re: delete first 7 characters in column in tMap

Anonymous — Thu, 08 Mar 2018 09:44:29 GMT

hi,

please find the below attachment and expression in tmap.

row3.Data.substring(0,7) ===(For first Five)

row3.Data.substring(7,StringHandling.LEN(row3.Data))===(For Last Data)

Capture4.PNG
Capture5.PNG

Re: delete first 7 characters in column in tMap

Anonymous — Thu, 08 Mar 2018 09:44:42 GMT

Hi,

The field has a length of 110. I can see this in the right bottom.

I've used the command (adres2 is the column):

StringHandling.LEN(row3.adres2)>7 ?substring(7,StringHandling.LEN(row3.adres2)):row3.adres2

Then I get this error:

Execution failed : Job compile errors
At least job "VBO_met_alle_functie" has a compile errors, please fix and export again.
Error Line: 6994
Detail Message: The method substring(int, int) is undefined for the type VBO_met_alle_functie
There may be some other errors caused by JVM compatibility. Make sure your JVM setup is similar to the studio.

What am I doing wrong?

Re: delete first 7 characters in column in tMap

manodwhb — Thu, 08 Mar 2018 09:48:04 GMT

use this

StringHandling.LEN(row3.test)>7 ? (row3.test).substring(7,StringHandling.LEN(row3.test)): row3.test

Re: delete first 7 characters in column in tMap

TRF — Thu, 08 Mar 2018 09:49:30 GMT

Or just this one!

row3.test != null && row3.test.length() > 7 ? row3.test.substring(7) : row3.test

Re: delete first 7 characters in column in tMap

Anonymous — Thu, 08 Mar 2018 09:54:42 GMT

Wow! What a great community in here!

Thank you for the help.

I've used this:

StringHandling.LEN(row3.adres2)>7 ? (row3.adres2).substring(7,StringHandling.LEN(row3.adres2)): row3.adres2 and it works.

Now a finale question. My understanding in Java is at novice level (I'm just learning it since last week). Do you care to explain how this statement is build?

Re: delete first 7 characters in column in tMap

manodwhb — Thu, 08 Mar 2018 09:56:52 GMT

first it will check the lenght is grater than 7 then it will do the substing else it will just pass the same value.

Re: delete first 7 characters in column in tMap

TRF — Thu, 08 Mar 2018 10:02:26 GMT

I prefer to explain mine as it is shorter:

row3.adres2 != null && row3.adres2.length() >= 7 ? row3.adres2.substring(7) : row3.adres2

If adres2 is nor null and its length is greater or equal to 7 characters, get 7 first characters, else get adres2 as it.

Re: delete first 7 characters in column in tMap

Anonymous — Thu, 08 Mar 2018 12:40:14 GMT

Both Manohar and TRF, thank you for the solutions and explanation. Much appreciated!

Re: delete first 7 characters in column in tMap

TRF — Thu, 08 Mar 2018 12:46:06 GMT

You're welcome