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
Forteen 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
Don't forget to give kudos/accept the solution when a replay is helpful.

View solution in original post

Highlighted
Fifteen Stars TRF
Fifteen 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

View solution in original post


All Replies
Forteen 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
Don't forget to give kudos/accept the solution when a replay is helpful.
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?

Forteen 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
Don't forget to give kudos/accept the solution when a replay is helpful.

View solution in original post

Fifteen Stars TRF
Fifteen 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?

Forteen 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
Don't forget to give kudos/accept the solution when a replay is helpful.
Highlighted
Fifteen Stars TRF
Fifteen 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

View solution in original post

Five Stars

Re: delete first 7 characters in column in tMap

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

Fifteen Stars TRF
Fifteen Stars

Re: delete first 7 characters in column in tMap

You're welcome


TRF

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 1

Learn how to do cool things with Context Variables

Blog

Migrate Data from one Database to another with one Job using the Dynamic Schema

Find out how to migrate from one database to another using the Dynamic schema

Blog

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog