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.

[vboppudi]

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

Regards,

[profuse]

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?

[vboppudi]

What is the error you are getting?

Regards,

[profuse]

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]

[Anonymous]

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)

[profuse]

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?

[vboppudi]

Please provide job screen shot.

Regards,

[Anonymous]

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

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

[profuse]

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?