delete first 7 characters in column in tMap

Five Stars

delete first 7 characters in column in tMap

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.


Accepted Solutions
Thirteen Stars

Re: delete first 7 characters in column in tMap

use this

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

Manohar B
Forteen Stars TRF
Forteen Stars

Re: delete first 7 characters in column in tMap

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.

 


TRF

All Replies
Thirteen Stars

Re: delete first 7 characters in column in tMap

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

Manohar B
Six Stars

Re: delete first 7 characters in column in tMap

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)

 

Five Stars

Re: delete first 7 characters in column in tMap

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?

Thirteen Stars

Re: delete first 7 characters in column in tMap

use this

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

Manohar B
Forteen Stars TRF
Forteen Stars

Re: delete first 7 characters in column in tMap

Or just this one!

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

TRF
Five Stars

Re: delete first 7 characters in column in tMap

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?

Thirteen Stars

Re: delete first 7 characters in column in tMap

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

Manohar B
Forteen Stars TRF
Forteen Stars

Re: delete first 7 characters in column in tMap

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.

 


TRF
Five Stars

Re: delete first 7 characters in column in tMap

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

Forteen Stars TRF
Forteen Stars

Re: delete first 7 characters in column in tMap

You're welcome


TRF