How to split a source field into 2 fields?

Four Stars

How to split a source field into 2 fields?

I have a CRM field as a source which I need to split into two.  Output is sql server.

Anything before : should load to crmDesc and anything after : should load to crmID

ABCDEFG:1

 

What is a java syntax that I can use?

 

 

Forteen Stars TRF
Forteen Stars

Re: How to split a source field into 2 fields?

Assume the input field is called myField, using a tMap you can do the following with the appropriate regex.
- for crmDesc: myField.replaceAll(":.*$", "")
- for crmID: myField.replaceAll("^.*:", "")

Using a tJavaRow, you can also use the String.split() method.

TRF
Four Stars

Re: How to split a source field into 2 fields?

Can you please give me a syntax how to do this in Java.

Sixteen Stars

Re: How to split a source field into 2 fields?

To use the String.split() method in a tJavaRow, first create your output columns. I have assumed you will use "crmDesc" and "crmID". Then (assuming your input data column is "myField") your code would look like below...

//Code generated according to input schema and output schema
String[] fields = input_row.myField!=null ? input_row.myField.split(":") : new String[0];


if(fields.length==2){
	output_row.crmDesc = fields[0];
	output_row.crmID = fields[1];
}else if(fields.length==1){
	output_row.crmDesc = fields[0];
	output_row.crmID = "";
}else{
	output_row.crmDesc = "";
	output_row.crmID = "";
}

I've covered scenarios where your input data is null, doesn't contain a ":" (I've assumed the data should go to the "crmDesc" column in that scenario), is as you expect and if more than 1 ":" character appears (it outputs blank fields for that). You can obviously adjust to your requirements.