[resolved] Replace string with "/" with ""

Highlighted
One Star

[resolved] Replace string with "/" with ""

I have a string zip column which is sometimes populated with a date MM/dd/yyyy coming from an excel spreadsheet.  When this occurs I get a field length error because talend is interpreting it as a date.  When this occurs I want to either null the field completely, remove the "/" character, or update it to "00000".   I tried adding the following in my tmap which does not replace the "/" with a space
//StringHandling.EREPLACE(row12.Zip, "/", "") 
//StringHandling.CHANGE(row12.Zip, "\\/", "") 
StringHandling.EREPLACE(row12.Zip, "/", "") 
I also tried adding a treplace component to my job but that also did not seem to work.  How can I either null or or remove this "/" from the data?
 

Accepted Solutions
One Star

Re: [resolved] Replace string with "/" with ""

Hi AdrienServian
Thank you so much for your response.  I used the following in my tmap, however I am still getting the field length error when inserting into the oracle table.  Do I need to add a java component in addition to adding this into my tmap?
row12.Zip.matches("{2}/{2}/{4}") ? null : row12.Zip
My tFileInputExcel component has the Zip field is set to String, however in the file itself there are two records where the field is set to date: 
from source file

tinputexcel

I increased the length in the oracle table to see how it is coming across, it is still coming across as a date string with a length >14 causing the error 

All Replies
Six Stars

Re: [resolved] Replace string with "/" with ""

Hi gisellerayner,
Have you tried native Java function:
String foo = "12/28/2017";
System.out.println(foo); --12/28/2017
System.out.println(foo.replaceAll("/", "")); --12282017

So for you in the tMap, if you choose the replace row12.Zip.replaceAll("/","");
If you want to replace it by null or "00000", I would use the matches method like that 

row12.Zip.matches("{2}/{2}/{4}") ? null : row12.Zip
row12.Zip.matches("{2}/{2}/{4}") ? "00000" : row12.Zip

Cheers,

Books are the quietest and most constant of friends; they are the most accessible and wisest of counsellors, and the most patient of teachers.
--Charles W. Eliot (1834 - 1926), The Happy Life, 1896
One Star

Re: [resolved] Replace string with "/" with ""

Hi AdrienServian
Thank you so much for your response.  I used the following in my tmap, however I am still getting the field length error when inserting into the oracle table.  Do I need to add a java component in addition to adding this into my tmap?
row12.Zip.matches("{2}/{2}/{4}") ? null : row12.Zip
My tFileInputExcel component has the Zip field is set to String, however in the file itself there are two records where the field is set to date: 
from source file

tinputexcel

I increased the length in the oracle table to see how it is coming across, it is still coming across as a date string with a length >14 causing the error 
One Star

Re: [resolved] Replace string with "/" with ""

I updated it to return date as null.  Thanks for your help
(TalendDate.isDate(row12.Zip, "EEE MMM dd HH:mm:ss zzz yyyy") == true) ? null : row12.Zip 

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