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

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