How Do I Return Text at the End of a String?

Support/help with CloverETL (4.9) and CloverDX (5.0 or newer) implementation problems

BuzzSurfer
Posts: 1
Joined: Mon Nov 09, 2020 6:24 pm

How Do I Return Text at the End of a String?

Postby BuzzSurfer » Mon Nov 09, 2020 6:31 pm

Hi,

I’m new to the forum and using Clover. However I have recently been given access to the software as part of my role.

I am looking for help with a problem I have. I have to separate part of a cell so the output is only text after a certain point.

For example, my input is ABCDEF.HAPPY and I need my output to be the text HAPPY. Essentially any text that appears after the full stop.

The first part of the input could be varying lengths, as could the text after the full stop. The only constant in all scenarios is the full stop.

Is anyone able to help please?

jandikovae
Posts: 64
Joined: Fri Nov 04, 2016 8:51 am

Re: How Do I Return Text at the End of a String?

Postby jandikovae » Mon Nov 16, 2020 11:38 pm

Hi,

When dealing with string fields, you might want to take a look at String Functions in the Documentation.

In a situation like yours, I prefer the function called SPLIT. The split() function splits a string from the first argument, based on a regular expression given as the second argument (the full stop in your case). As a result, you get an array of values, and you can address each one of them using a sequence number (they are numbered starting with zero).

Therefore, in the Reformat component, you can resolve your example situation using the following transformation:

Code: Select all

string[] SplitFieldArray = split($in.0.field1,"\\.");
$out.0.field1 = SplitFieldArray[1];


Please, note that I have "escaped" the character "." with double back-slash, because in this function the second argument is defined by regular expressions and the single full stop would then mean "any character".

In addition, if you know exactly the length of your resulting string (for example you know that you are interested in the last 5 characters in the string), you can use a function RIGHT. The right() function returns the substring of the length specified as the second argument counted from the end of the string specified as the first argument. Your CTL2 transformation in the Reformat would then be:

Code: Select all

$out.0.field1 = right($in.0.field1,5);


And it would result in HAPPY again. :)

Please take a look and don't hesitate to ask in case you have any follow-up questions. Have a nice day, Eva
---
Eva Jandikova
CloverCARE Support
CloverDX

Visit us online at http://www.cloverdx.com


cron