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

[profuse]

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.

[Anonymous · Accepted solution]

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

[profuse]

That Worked!!!!!

Thank You So Much.... you rock!

[Anonymous]

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 

[profuse]

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

[Anonymous]

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.

[profuse]

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".

[Anonymous]

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.