Hi Jean,
I guess following CTL functions could solve your problem:
string replace(string arg, string regex, string replacement);
The replace(string, string, string) function takes three string arguments - a string, a regular expression, and a replacement - and replaces all regex matches inside the string with the specified replacement string. All parts of the string that match the regex are replaced. The user can also reference the matched text using a backreference in the replacement string. A backreference to the entire match is indicated as $0. If there are capturing parentheses, specifics groups as $1, $2, $3, etc can be referenced.
replace("Hello","[Ll]","t") returns "Hetto"
replace("Hello", "e(l+)", "a$1") returns "Hallo"
Important - please beware of similar syntax of $0, $1 etc. While used inside the replacement string it refers to matching regular expression parenthesis (in order). If used outside a string, it means a reference to an input field. See other example:
replace("Hello", "e(l+)", $0.name) returns HJohno if input field "name" on port 0 contains the name "John".
A modifier cen be used at the start of the regular expression: (?i) for case-insensitive search, (?m) for multiline mode or (?s) for “dotall” mode where a dot (“.”) matches even a newline character
replace("Hello", "(?i)L", "t") will produce Hetto while replace("Hello", "L", "t") will just produce Hello
If the first argument of the function is null, the function returns null. If the regexp pattern is null, the function fails with error. If the third argument is null, the function fails with error, unless the specified regexp doesn’t match the first input.
string translate(string arg, string searchingSet, string replaceSet);
The translate(string, string, string) function replaces the characters given in the second string of the first argument with characters from the third string. Thus the function call
translate('Hello','eo','iy') results in the string Hilly.
translate($0.name,'abcde',edcba') results in the source string/field with reversed letters a->e , b->d, c->c, d->b, e->a.
If one or both of the second or the third argument is null, the function fails with error.
If the input of the function is null, the function returns null.
All string manipulation functions are available in CloverETL’s manual.