Four Stars

How to replace everything after the first comma in a string?

Hi,

Could you please help.

I have a column that contains the following: Rofuse, MD, MPH, FRCPC

 

I want to delete everything after the fist comma so it would only contain the word Rofuse.

 

How would I do this using tReplace or tMap?

 

Thank you.

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Eleven Stars

Re: How to replace everything after the first comma in a string?

Your data doesn't have a comma in it. Try this to mitigate for a lack of a comma....

 

row1.LastName.indexOf(',') != -1 ? row1.LastName.substring(0, row1.LastName.indexOf(',')) : row1.LastName
Rilhia Solutions
16 REPLIES
Six Stars

Re: How to replace everything after the first comma in a string?

Hi, try below approach. Create one output port in tMap and use left, index functions.

 

 

Capture.PNG

Regards,

 

 

Veeranjaneyulu Boppudi
Four Stars

Re: How to replace everything after the first comma in a string?

Hi,

The 'Rofuse, MD, MPH, FRCPC' is in a column called LastName. In Expression, I created the following:

 

StringHandling.LEFT(row1.LastName,StringHandling.INDEX(row1.LastName,","))

 

But it does not run.

 

Can you help?

Six Stars

Re: How to replace everything after the first comma in a string?

What is the error you are getting?

 

Regards,

Veeranjaneyulu Boppudi
Four Stars

Re: How to replace everything after the first comma in a string?

Here is the message:

Exception in component tMap_1

java.lang.StringIndexOutOfBoundsException: String index out of range: -1

      at java.lang.String.substring(Unknown Source)

      at routines.StringHandling.LEFT(StringHandling.java:221)

      at dhdproject.step1normalizelinkedin_0_1.Step1NormalizeLinkedIn.tFileInputDelimited_1Process(Step1NormalizeLinkedIn.java:3248)

      at dhdproject.step1normalizelinkedin_0_1.Step1NormalizeLinkedIn.runJobInTOS(Step1NormalizeLinkedIn.java:4737)

      at dhdproject.step1normalizelinkedin_0_1.Step1NormalizeLinkedIn.main(Step1NormalizeLinkedIn.java:4594)

[statistics] disconnected

Job Step1NormalizeLinkedIn ended at 17:26 05/10/2017. [exit code=1]

Eleven Stars

Re: How to replace everything after the first comma in a string?

Both of these methods work.....

 

Java (in a tMap, etc)

row.yourvalue.substring(0, row.yourvalue.indexOf(','))

...or if you want to use Regex in a tReplace, you can use the following regex .....

Pattern

",(.*)"

Replace

""

For the regex you will need to use the advanced settings of the tReplace ("Advanced mode" tick box)

Rilhia Solutions
Four Stars

Re: How to replace everything after the first comma in a string?

Hi,

In the tMap expression I coded:

row1.LastName(0, row1.LastName.indexOf(','))

 

When I run the job it errors with the following message:

Exception in thread "main" java.lang.Error: Unresolved compilation problem:
The method LastName(int, int) is undefined for the type Step1NormalizeLinkedIn.row1Struct

at dhdproject.step1normalizelinkedin_0_1.Step1NormalizeLinkedIn.tFileInputDelimited_1Process(Step1NormalizeLinkedIn.java:3248)
at dhdproject.step1normalizelinkedin_0_1.Step1NormalizeLinkedIn.runJobInTOS(Step1NormalizeLinkedIn.java:4736)
at dhdproject.step1normalizelinkedin_0_1.Step1NormalizeLinkedIn.main(Step1NormalizeLinkedIn.java:4593)

 

Can you help?

Six Stars

Re: How to replace everything after the first comma in a string?

Please provide job screen shot.

Regards,

 

 

Veeranjaneyulu Boppudi
Eleven Stars

Re: How to replace everything after the first comma in a string?

You made a mistake with your code. This is what you should have used...

row1.LastName.substring(0, row1.LastName.indexOf(','))
Rilhia Solutions
Four Stars

Re: How to replace everything after the first comma in a string?

I just code the tMap Expression:

row1.LastName.substring(0, row1.LastName.indexOf(','))

And I get the following error message:

 

Exception in component tMap_1
java.lang.StringIndexOutOfBoundsException: String index out of range: -1
at java.lang.String.substring(Unknown Source)
at dhdproject.step1normalizelinkedin_0_1.Step1NormalizeLinkedIn.tFileInputDelimited_1Process(Step1NormalizeLinkedIn.java:3249)
at dhdproject.step1normalizelinkedin_0_1.Step1NormalizeLinkedIn.runJobInTOS(Step1NormalizeLinkedIn.java:4737)
at dhdproject.step1normalizelinkedin_0_1.Step1NormalizeLinkedIn.main(Step1NormalizeLinkedIn.java:4594)
[statistics] disconnected
Job Step1NormalizeLinkedIn ended at 19:05 05/10/2017. [exit code=1]

 

Can You Help?

Eleven Stars

Re: How to replace everything after the first comma in a string?

Your data doesn't have a comma in it. Try this to mitigate for a lack of a comma....

 

row1.LastName.indexOf(',') != -1 ? row1.LastName.substring(0, row1.LastName.indexOf(',')) : row1.LastName
Rilhia Solutions
Four Stars

Re: How to replace everything after the first comma in a string?

That Worked!!!!!

 

Thank You So Much.... you rock!

Eleven Stars

Re: How to replace everything after the first comma in a string?

Not a problem. A lot of your issues were identified by the error stacks. It will really help you working with Talend, to spend a bit of time learning Java (or improving it, if you already have a bit of Java). While technically not needed for Talend, Java really opens quite a few doors 

Rilhia Solutions
Four Stars

Re: How to replace everything after the first comma in a string?

Hi Eleven Stars,

 

In the tmap expression is there a way to do IF ELSE logic?

 

Yesterday we checked for a comma in the LastName:

row1.LastName.indexOf(',') != -1 ? row1.LastName.substring(0, row1.LastName.indexOf(',')) : row1.LastName

 

Is there also a way to check for space in the same expression using IF ELSE?

row1.LastName.indexOf(' ') != -1 ? row1.LastName.substring(0, row1.LastName.indexOf(' ')) : row1.LastName

 

 

Eleven Stars

Re: How to replace everything after the first comma in a string?

Are you testing for a comma OR a space exclusively? What if there is a command AND a space? What would you do? You need to decide upon that, but you can achieve a "else if" using something like below....

 

row1.LastName.indexOf(',') != -1 ? row1.LastName.substring(0, row1.LastName.indexOf(',')) : row1.LastName.indexOf(' ') != -1 ? row1.LastName.substring(0, row1.LastName.indexOf(' ')) : row1.LastName

This will check for a comma first and if it finds one, will process the String expecting the comma. If it doesn't find a comma, it then checks for a space. If there is a comma and a space, the comma part of the expression will be run since it processing this in order.

Rilhia Solutions
Four Stars

Re: How to replace everything after the first comma in a string?

Rilhia,

 

You are brilliant. I researched the what if and tried everything I could get my hands on and nothing worked. Your solution works great!

Thank You!!!!

 

Now I'm on the hunt for code that says if one column has the letters "MD" in it then fill another new column with the word "Physician".

 

Eleven Stars

Re: How to replace everything after the first comma in a string?

Rather than give you the answer to your next question, I'll point you in a good direction. In tMap components you can use tMap variables (the box between the input and output tables). You can create as many of these as you like, using them to calculate things like this. You need to check if a value is held in one column and if it is, add another value to a different column. You can use your input columns as many times as you want in a tMap. Have a play around using a simple job with a single tMap and you will figure it out.
Rilhia Solutions