Transform - Text

Case

Change the case of text, international style using a Collation to specify language.  Specification of lower, title, or upper case overrides any nocase setting in the Collation.

• lower - Change all characters to lower case.
• title - Windows style title case: Convert the initial letter of each word in the source field  into upper case and convert other characters in the word into lower case, except that words which are already all upper case are left all upper case.  
• upper - Change all characters to upper case.

Compose

Compose a text value conforming to the syntax of the specified text standard: 

• geojson - Compose a GeoJSON text geometry specification from a geometry field and a coordinate system name.

• gml - Compose a GML text geometry specification from a geometry field and a coordinate system name.  
• url - Compose a URL string from Scheme (http, https, ftp, etc.), Host, Port, User, Password, Path and Extra values.

• url from base and relative - Compose a URL string by appending a Relative path URL string to an absolute URL Base string.

• wkt - Compose a WKT text geometry specification from a geometry field.

Concatenate

Combine text from the source field with other text by adding at the beginning or the end text from another field, a value or an expression, using an optional Separator string of characters.  The Separator string is added only if both values are not empty.

• end - Add specified text, preceded by any text in the Separator box, to the beginning of the source text.
• start - Add specified text, followed by any text in the Separator box, to the beginning of the source text.

The text in the Separator box is often just a single character, such as a space character, but it could be a string of more than one character.   It could be a comma character followed by a space, as often is used when concatenating field values together when building a list of field values separated by a comma.

For example, given a Value of The and a Separator that is just the space character, a source text of Netherlands is transformed into The Netherlands.    Given a Value of The and a Separator box containing Republic of with space characters at the beginning and end, we transform Ireland to The Republic of Ireland.

Because the separator string is added only when both texts (the original field, and that specified) are not empty, that makes it easy to build sequences of comma separated items.  For example, if we concatenate a field to the starting field using a Separator of ', ' (a comma character and a space character),  we can safely do the concatenation even if the starting field is empty, because in that case the ', ' will not be added to start the list.

For example, if the starting text is John and we concatenate Paul using a Separator of ', ' (a comma character and a space character), the result will be John, Paul.  

If the starting text is empty and we concatenate Paul using a Separator of ', ' (a comma character and a space character), the result will be Paul  - it will not be , Paul with an unwanted comma character and space character preceding Paul.

Copy

Copy into the Result destination the text value or quantity extracted or computed from the source field text.

• geojson coordinate system - extract a coordinate system from a GeoJSON string.
• geojson geometry - Convert GeoJSON text representation of geometry  into Manifold or WKB binary geometry.
• gml coordinate system - extract a coordinate system from a GML string.
• gml geometry - Convert GML text representation of geometry  into Manifold or WKB binary geometry.
• json array value - extract the specified JSON array from a JSON string.
• json named value - extract the specified JSON named object from a JSON string.
• number of characters - Copy the number of characters into a specified numeric Result destination.
• text - Copy the text from the source field  into a specified Result destination using the specified text type.
• url extra parts - extract extra parts from a URL string. 
• url host - extract the host from a URL string.  
• url password - extract the password from a URL string. 
• url path - extract the path from a URL string. 
• url port - extract the port from a URL string.  
• url scheme - extract the scheme from a URL string. 
• url user - extract the user from a URL string.  

• wkt geometry - Convert WKT text representation of geometry  into Manifold or WKB binary geometry.

Encrypt

Encrypt or decrypt text in the source field using a password taken from a field, a given value, or from an expression.

• decrypt - Decrypt text using the specified password.
• encrypt - Encrypt text using the specified password.

The transform utilizes a simple AES256 based encryption mechanism.  The key is first hashed using 128-bit SHA and then the hash is used to encrypt the data. 

Expression

Save the result of the given expression into the specified Result destination, creating new fields using the specified type.

Pad

Pad text from the source field to the specified length by adding at the beginning or the end repeated text from another field, a value or an expression.  The Repeat text is often a single character.

• end - Append the padding string to the end of the source text.
• start - Prepend the padding string to the beginning of the source text.

Reduce

Cut out portions of text from the source field and save to the specified Result destination.  The Start numbering is zero based: starting at the first letter of the text is a Start number of 0, and starting at the second letter of text is a Start number of 1.

• cut - Reduce to the text portion of given length beginning at the Start character.
• cut to end - Reduce to the text portion beginning at the Start character and continuing to the end of the text.
• end - Reduce to the last number of characters given in the Length.  Using a Length of 5 would cut out and use the last 5 characters of the text.
• regular expression occurrence - Given an Occurrence number (zero based counting), and a regular expression, cut out the given occurrence of text that matches the regular expression.  For example, an Occurrence number of 1 means the second occurrence.  If the regular expression was e. (the letter e followed by any single character) and the starting text was Netherlands, the text would be reduced to er, which is the second occurrence in Netherlands of the letter e followed by any single character.   Using an Occurrence of 0 (the first occurrence) of the same regular expression would result in et, which is the first instance of the letter e followed by any character.  If the starting text was France, the result would be an empty text since there is no e followed by at least one character in France.  Note that an empty text string is not the same as a NULL.
• start - Reduce to the first number of characters given in the Length.  Using a Length of 5 would cut out and use the first 5 characters of the text.

Replace

Replace portions of text with other text.   The text to be replaced can be specified either as plain text or as a regular expression.

• regular expression - Search for the regular expression pattern given in Search for and replace all occurrences of that regular expression pattern with the text given in Replace with.
• regular expression occurrence - Search for the regular expression pattern given in Search for and replace only the occurrence (zero based counting) given in Occurrence of that regular expression pattern with the text given in Replace with.  For example, search for e. and replace with X with an Occurrence of 0 replaces the first instance of the letter e followed by any letter with X, to transform Netherlands into NXherlands, and to transform Germany into GXmany, but not to alter France, where the only letter e is not followed by any letters.
• text - Search for the text given in Search for and replace all occurrences with the text given in Replace with.
• text occurrence - Search for the text given in Search for and replace only the occurrence (zero based counting) given in Occurrence of that text with the text given in Replace with.  For example, search for e and replace with X with an Occurrence of 1 replaces the second occurrence of e with X, to transform Netherlands into NethXrlands.

Reverse

Reverse the order of characters in the text.   For example, Netherlands becomes sdnalrehteN.

Trim

Remove characters from the start, the end, or from both the start and end of a string, removing either whitespace characters, any character that occurs in a custom list of characters or both whitespace and custom list characters.   By default the template trims whitespace characters from the start and the end of the source text.

• end - Trim any specified characters from the end of the source text.
• start - Trim any specified characters from the start of the source text.
• start and end - Trim any specified characters from both the start and the end of the source text.

The Characters box allows specifying what characters will be trimmed.

• custom - Pop open a Value box, and any characters in that box will be trimmed.  
• whitespace - Trim any whitespace characters.  A whitespace character is a character like a space character or a tab, but also non-printing characters such as line feed, form feed, and carriage return.
• whitespace and custom - Trim both whitespace characters as well as any character that appears in the Value box.

The Value box that appears for custom and whitespace and custom choices allows specifying a field in the table, a value that is a list of characters, or an expression.  For example, using the value choice,  to trim both upper case E and lower case e from a text, enter Ee into the Value box.  When used with start and end, that will trim Europe to urop. To trim all lower case and upper case vowels, enter aeiouyAEIOUY into the Value box.  When used with start and end, that will trim Europe to rop.  Since both upper case E and lower case u are found in the Value box list, both characters will be trimmed from the start of the text.