Transform Templates - Text

This topic discusses application of the Transform Dialog with Tables on a text field in a table.  See the Transform Dialog and Transform Templates topics for use and for links to lists of templates for other data types for tables and use of the Transform Dialog with other components, such as Drawings and Images.

 

In the examples below, columns shown in blue preview color are being previewed by the Transform dialog to show what the template would do.

Tables, Text Types

Compose Base64 String

Takes any value type and converts it to a base64-encoded string.

 

Example: Take the Description field and Compose Base64 String to put into Temp_text.

il_trans_comp_base64_text.png

Base64 encoding represents binary values as an ASCII string utilizing 64 printable characters.   Manifold uses standard 'base64' encoding for RFC 3458 or RFC 4648, as has become ubiquitous with browsers.  When starting with text the input string is first converted into UTF8 and then expressed using base64 encoding.

Compose GeoJSON

Provides a way to extract geometry from a Manifold geometry type, such as a geom, into a common text representation of the geometry, GeoJSON.  Given a geometry field, such as a geom, and a coordinate system specified in the System box, create GeoJSON text for that object.   

 

il_trans_drawing_objects.png

 

Example: Using the table for the drawing above, take the Geom field and a System value of 'EPSG:9840' use Compose GeoJSON to put into the field called JSON.

il_trans_comp_json_text.png

The full value of the JSON string generated for the first record is

 

{ "type": "Polygon", "coordinates": [ [ [ -526.5, 268.5 ], [ -459.5, 333.5 ], [ -429.5, 222.5 ], [ -526.5, 268.5 ] ] ], "crs": { "type": "name", "properties": { "name": "EPSG:9840" } }, "bbox": [ -526.5, 222.5, -429.5, 333.5 ] }

 

We use an EPSG code to show that can be done, which is a great convenience for geoms that use EPSG codes to specify their projections.    

 

A much longer JSON results in cases where the FieldCoordSystem.Geom property of a table is not an EPSG code but instead is a custom definition or one constructed from the name of a coordinate system.  

 

For example, suppose the value of the FieldCoordSystem.Geom property of the Objects table above was:

 

{ "Axes": "XYH", "Base": "World Geodetic 1984 (WGS84)", "CenterLat": 0, "CenterLon": 0, "Eccentricity": 0.08181919084262149, "MajorAxis": 6378137, "Name": "Orthographic", "System": "Orthographic", "Unit": "Meter", "UnitScale": 1, "UnitShort": "m" }

 

We could have copied that and entered it into the System box of the Transform dialog (between single quotes).    In that case the JSON string generated for the first record would have been the considerably longer:

 

{ "type": "Polygon", "coordinates": [ [ [ -526.5, 268.5 ], [ -459.5, 333.5 ], [ -429.5, 222.5 ], [ -526.5, 268.5 ] ] ], "crs": { "type": "name", "properties": { "name": "{ \"Axes\": \"XYH\", \"Base\": \"World Geodetic 1984 (WGS84)\", \"CenterLat\": 0, \"CenterLon\": 0, \"Eccentricity\": 0.08181919084262149, \"MajorAxis\": 6378137, \"Name\": \"Orthographic\", \"System\": \"Orthographic\", \"Unit\": \"Meter\", \"UnitScale\": 1, \"UnitShort\": \"m\" }" } }, "bbox": [ -526.5, 222.5, -429.5, 333.5 ] }

 

No wonder EPSG codes are popular!

Compose GML

Provides a way to extract geometry from a Manifold geometry type, such as a geom, into a common text representation of the geometry, GML.  Given a geometry field, such as a geom, and a coordinate system specified in the System box, create GML text for that object.   

 

il_trans_drawing_objects.png

 

Example: Using the table for the drawing above, take the Geom field and a System value of 'EPSG:9840' use Compose GML to put into the field called GML.

il_trans_comp_json_text.png

The full value of the GML string generated for the first record is

 

<?xml version="1.0" encoding="UTF-8"?>

<gml:Surface xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:gml="http://www.opengis.net/gml/3.2" xsi:schemaLocation="http://www.opengis.net/gml/3.2 http://schemas.opengis.net/gml/3.2.1/gml.xsd" srsName="EPSG:9840" srsDimension="3">

<gml:patches>

<gml:PolygonPatch interpolation="planar">

<gml:exterior>

<gml:LinearRing>

<gml:posList>-526.5 268.5 25 -459.5 333.5 25 -429.5 222.5 25 -526.5 268.5 25</gml:posList>

</gml:LinearRing>

</gml:exterior>

</gml:PolygonPatch>

</gml:patches>

</gml:Surface>

 

We use an EPSG code to show that can be done, which is a great convenience for geoms that use EPSG codes to specify their projections.    A much longer JSON results in cases where the FieldCoordSystem.Geom property of a table is not an EPSG code but instead is a custom definition or one constructed from the name of a coordinate system.

 

For example, suppose the value of the FieldCoordSystem.Geom property of the Objects table above was:

 

{ "Axes": "XYH", "Base": "World Geodetic 1984 (WGS84)", "CenterLat": 0, "CenterLon": 0, "Eccentricity": 0.08181919084262149, "MajorAxis": 6378137, "Name": "Orthographic", "System": "Orthographic", "Unit": "Meter", "UnitScale": 1, "UnitShort": "m" }

 

We could have copied that and entered it into the System box of the Transform dialog (between single quotes).    In that case the GML string generated for the first record would have been the considerably longer:

 

<?xml version="1.0" encoding="UTF-8"?>

<gml:Surface xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:gml="http://www.opengis.net/gml/3.2" xsi:schemaLocation="http://www.opengis.net/gml/3.2 http://schemas.opengis.net/gml/3.2.1/gml.xsd" srsName="{ &quot;Axes&quot;: &quot;XYH&quot;, &quot;Base&quot;: &quot;World Geodetic 1984 (WGS84)&quot;, &quot;CenterLat&quot;: 0, &quot;CenterLon&quot;: 0, &quot;Eccentricity&quot;: 0.08181919084262149, &quot;MajorAxis&quot;: 6378137, &quot;Name&quot;: &quot;Orthographic&quot;, &quot;System&quot;: &quot;Orthographic&quot;, &quot;Unit&quot;: &quot;Meter&quot;, &quot;UnitScale&quot;: 1, &quot;UnitShort&quot;: &quot;m&quot; }" srsDimension="3">

<gml:patches>

<gml:PolygonPatch interpolation="planar">

<gml:exterior>

<gml:LinearRing>

<gml:posList>-526.5 268.5 25 -459.5 333.5 25 -429.5 222.5 25 -526.5 268.5 25</gml:posList>

</gml:LinearRing>

</gml:exterior>

</gml:PolygonPatch>

</gml:patches>

</gml:Surface>

 

Compose Hex String

Takes any value type and converts it to a hexadecimal-encoded string.

 

Example: Take the Description field and Compose Hex String to put into Temp_text.

il_trans_comp_hex_text.png

Hexadecimal encoding represents binary values as an ASCII string utilizing 16 printable characters representing ubiquitous base-16 hexadecimal computer notation.  Unicode text will require two hex codes per character.

Compose WKT

Provides a way to extract geometry from a Manifold geometry type, a geom, into a well known text representation of the geometry, WKT.  Given a geom field create WKT text for that object.

 

Example: Take the Geom field and use Compose WKT to put into TextValue.

 

il_trans_comp_wkt_text.png

 

For example, a geom field that contains a point at Longitude 10.687 and Latitude 59.8876 would result in the text

 

POINT(10.687 59.8876)

Concatenate

Take the source field string and append to it the Concatenate field string and put the result into the target field.

 

Example: Take Status and Concatenate to it the Comment field and put the result into Temp_text.

il_trans_concatenate_text.png

The result is logical from an SQL perspective but probably not what we want.  Where there is a NULL in the source string we would like just the string value that is to be concatentated to be added to an empty string so the result is just the string that is concatentated.

 

We can accomplish that by using the SQL COALESCE function within the combo box for the source field.  Instead of just choosing Status form the pull down list of fields, into that combo box we write the expression COALESCE(Status, '') and, as before, we choose the Comment field as the field to be concatenated.  

 

Example: Take COALESCE(Status, '') and Concatenate to it the Comment field and put the result into Temp_text.

il_trans_concatenate2_text.png

What the  COALESCE(Status, '')  expression means is to use the value of the Status field and if that value is NULL, then instead use the argument, which in this case is an empty string, that is, two single quote characters '' with nothing between them.

 

See the Example: Construct JSON String using Select and Transform topic for an example of Concatenate in action.

Copy

Copy the source field into the target field.

 

Example: Copy the Comment field into Temp_text.

il_trans_copy_text.png

 

Copy is also used for conversions such as converting geom types to WKT text type.

 

Example: Copy into Temp_text the result of GeomWkt([Geom]) entered into the source box.

il_trans_copy_geom_text.png

Decrypt

Takes a string value encrypted with Encrypt and decrypts it into a string using the specified key.

 

Example: Take the encrypted Temp_text field and Decrypt using the key 'radian' to put into Results

il_trans_decrypt_text.png

Encrypt

Takes a string value and encrypts it into a string using the specified key.

 

Example: Take the Description field and Encrypt using the key 'radian' to put into Temp_text.

il_trans_encrypt_text.png

Manifold 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.    Do not use the default key of 'password' or an easily-guessed key such as '12345'.

Escape

See the Transform: Escape Templates topic.

 

Escape, JSON

 

Escape, Encode Special Characters

 

JSON Array

A means to extract arrays that are embedded into JSON strings for a named JSON entity.  Scan the source field to find the specified name and if it exists and has an array value put the array value into the text target field.

JSON Object

A means to extract objects that are embedded into JSON strings for a named JSON entity.  Scan the source field to find the specified name and if it exists and has an object value put the object value into the text target field.

JSON String

A means to extract strings that are embedded into JSON strings for a named JSON entity.  Scan the source field to find the specified name and if it exists and has an string value put the string value into the text target field.

 

Example: Find the JSON String for the Name 'Site' within the field JSON_Info and put it into Temp_text.

il_trans_json_string_text.png

Lower Case

Copy the source field into the target field converting all characters to lower case.

 

Example: Copy Comment in all lower case into Temp_text.

il_trans_lower_case_text.png

Lower Case, Intl

Same as Lower Case but with optional specification of the language used.

Maximum Value

Compare text sort order of source field and comparison field.  Copy into the target field the contents of the field which has a bigger sort order number, that is, sorts after the other field.

 

Example: Put in Temp_text the Maximum Value result of comparing Town to Country.

il_trans_max_value_text.png

In the first record a text value beginning with Wij... sorts after a text value beginning with Net... so the content of the Town field is copied into Temp_text. In the second record a text value beginning with Swi... sorts after a text value beginning with La ... so the content of the Country field is put into Temp_text.

Minimum Value

Compare text sort order of source field and comparison field.  Copy into the target field the contents of the field which has a lesser sort order number, that is, sorts before the other field.

 

Example: Put in Temp_text the Minimum Value result of comparing Town to Country.

il_trans_min_value_text.png

In the first record a text value beginning with Net... sorts before a text value beginning with Wij... so the content of the Country field is copied into Temp_text. In the second record a text value beginning with La ... sorts before a text value beginning with Swi... so the content of the Town field is put into Temp_text.

Pad End

Copy the source field into the target field to the specified length, as necessary truncating or padding at the end with the specified string.

 

Example: Copy Country into Temp_text to the Length of 20 using '.' for Pad End.

il_trans_pad_end20_text.png

All of the resulting Temp_text strings are 20 characters in length with '.' characters added at the end to pad the lengths of strings from Country that were less than 20 characters.

 

Example: Copy Country into Temp_text to the Length of 20 using '-_' for Pad End.

il_trans_pad_end21_text.png

Multiple characters can be used in the padding string.

 

Example: Copy Country into Temp_text to the Length of 3 using '-_' for Pad End.

il_trans_pad_end3_text.png

When the source string is longer than the specified Length it will be truncated.

Pad Start

Copy the source field into the target field to the specified length, as necessary truncating or padding at the beginning with the specified string.

 

Example: Copy Country into Temp_text to the Length of 20 using '.' for Pad Start.

il_trans_pad_start20_text.png

All of the resulting Temp_text strings are 20 characters in length with '.' characters added at the beginning to pad the lengths of strings from Country that were less than 20 characters.

 

Example: Copy Country into Temp_text to the Length of 20 using '-_' for Pad Start.

il_trans_pad_start21_text.png

Multiple characters can be used in the padding string.

 

Example: Copy Country into Temp_text to the Length of 3 using '-_' for Pad Start.

il_trans_pad_start3_text.png

When the source string is longer than the specified Length it will be truncated.  Note that the result is the same as for a truncated Pad End.

Parse Base64 String

Takes a base64-encoded string and converts into the original value type.   Target value type must be the same as that used to originally create the  encoded string.  For example, if we encode a varchar string into a bin64 string the Parse Base64 String will restore the original value only if the target is also a varchar string.

 

Example: Take the Temp_text field and Parse Base64 String to put into Parsed.

il_trans_parse_base64_text.png

 

Parse GeoJSON Coordinate System

Takes a GeoJSON string and extracts the coordinate system, if any, specified within that string.

 

Example: Take the field called JSON, which contains GeoJSON strings, and use Parse GeoJSON Coordinate System to extract the coordinate system from the strings and write that to the field called Text.   Both examples below use the JSON field populated in the examples for the Compose GeoJSON template in this topic.

 

In the first screen below we use the case where Compose GeoJSON was utilized with an EPSG code as the System value to create the JSON field values.

il_trans_parse_json_coord1_text.png

As we can see above, the template correctly extracts the EPSG coordinate system that was specified.

 

In this next screen we use the case where Compose GeoJSON was utilized with a lengthy textual coordinate system specification that was copied out of the properties of the Object table.

il_trans_parse_json_coord2_text.png

The template again correctly extracts the coordinate system that was specified.

Parse GML Coordinate System

Takes a GML string and extracts the coordinate system, if any, specified within that string.

 

Example: Take the field called GML, which contains GML strings, and use Parse GML Coordinate System to extract the coordinate system from the strings and write that to the field called Text.   Both examples below use the GML field populated in the examples for the Compose GML template in this topic.

 

In the first screen below we use the case where Compose GML was utilized with an EPSG code as the System value to create the GML field values.

il_trans_parse_gml_coord1_text.png

As we can see above, the template correctly extracts the EPSG coordinate system that was specified.

 

In this next screen we use the case where Compose GML was utilized with a lengthy textual coordinate system specification that was copied out of the properties of the Object table.

il_trans_parse_gml_coord2_text.png

The template again correctly extracts the coordinate system that was specified.

Parse Hex String

Takes a hexadecimal-encoded string and converts to the original value type.  Target value type must be the same as that used to originally create the  encoded string.  For example, if we encode a varchar string into a bin64 string the Parse Hex String will restore the original value only if the target is also a varchar string.

 

Example: Take the Temp_text field and Parse Hex String to put into Parsed.

il_trans_parse_hex_text.png

 

Regexp Match

Copy into the target field the first instance of any match to the given regular expression Pattern.  Check the Ignore case box to treat upper case and lower case letters as the same.

 

Example: Put into Temp_text the Regexp Match using Country and a regexp Pattern of '.'.

il_trans_regexp_match_text.png

The regular expression Pattern '.' matches any character.  The first character found, that is, the first character in the string, is put into Temp_txt.

 

Example: Put into Temp_text the Regexp Match using Country and a regexp Pattern of 'er.*'.

il_trans_regexp_match2_text.png

The regular expression Pattern 'er.*' matches any occurrence of er followed by one or more characters.

 

Example: Put into Temp_text the Regexp Match using Country and a regexp Pattern of 'n..' with Ignore case checked.

il_trans_regexp_match3_text.png

The regular expression Pattern 'n..' with the Ignore case option checked matches any occurrence of N or n followed by any two characters.  That pattern occurs twice  in Netherlands, with the first instance being Net.   In France the first and only  occurrence of the pattern is the nce letters at the end.  In Germany and Switzerland the pattern does not occur since there is only one letter after the single n in Germany and Switzerland.

Regexp Match, Nth

Given a text source field and a regular expression Pattern and an Occurrence number, n, (zero based counting, with the first Occurrence being 0, the second being 1 and so on), copy into the target field the nth instance of any match to the given regular expression.  Check the Ignore case box to treat upper case and lower case letters as the same.

 

Example: Put into Temp_text the Regexp Match using Country, a regexp Pattern of '.' and an Occurrence of 1.

il_trans_regexp_match_nth_text.png

The regular expression Pattern '.' matches any character.  Counting starting from zero, an Occurrence of 1 means the second instance of the pattern, that is, the second character in the country name.

 

Example: Put into Temp_text the Regexp Match using Country,  a regexp Pattern of 'n..' and an Occurrence of 1 with Ignore case checked.

il_trans_regexp_match_nth3_text.png

The regular expression Pattern 'n..' with the Ignore case option checked matches any occurrence of N or n followed by any two characters.  An Occurrence of 1 means the second instance (counting 0, 1, 2,...) of the pattern, which occurs twice only in Netherlands, the nds letters.   There is no second occurrence in the other country names.  In fact, in Germany and Switzerland there is not even a first occurrence since there is only one letter after the single n in Germany and Switzerland.

Replace Regexp, All

When we really want to replace something, this is the way.  In the source field find all occurrences of the given regular expression Pattern, replace them with the Replace with text and put the result into the target field.  If no Pattern is found, copy the source field into the target. Check the Ignore case box to treat upper case and lower case letters as the same.

 

Example: Put into Temp_text the Replace Regexp, All result using Country, a regexp Pattern of 'land' and a Replace with text of 'water'.

il_trans_replace_regexp_all_text.png

 

Example: Put into Temp_text the Replace Regexp, All result using Country, a regexp Pattern of 'e' and a Replace with text of '*'.

il_trans_replace_regexp_all2_text.png

A regexp Pattern of 'e' matches any single occurrence of the letter e.  This example shows how the Pattern is a regular expression but the Replace with text is just a string literal.  Whatever we put within the two single quote ' characters is taken as a literal string.  

 

For very intricate replacements, we could also put an SQL expression that evaluates to a string into the Replace with combo box. For example, we might use a regular expression to find dates in a field and to replace them with a computed date.  See the example below for the  Replace Text, All template.

Replace Regexp, Nth

Given a text source field and a regular expression Pattern, a Replace with text string and an Occurrence number, n, (zero based counting, with the first Occurrence being 0, the second being 1 and so on), find the nth instance of any match to the given regular expression Pattern, replace it with the Replace with text and put the result into the target field.  If no nth instance of Pattern is found, copy the source field into the target. Check the Ignore case box to treat upper case and lower case letters as the same.

 

Example: Put into Temp_text the Replace Regexp, Nth result using Country, a regexp Pattern of 'e', a Replace with text of '*' and an Occurrence of 1.

il_trans_replace_regexp_nth_text.png

 

Example: Put into Temp_text the Replace Regexp, Nth result using Country, a regexp Pattern of 'n.', a Replace with text of 'xy' and an Occurrence of 1 with Ignore case checked.

il_trans_replace_regexp_nth2_text.png

A regexp Pattern of 'n.' with Ignore case checked matches any occurrence of N or n followed by one letter.  An Occurrence of 1 tells us (zero based counting) to look at the second occurrence of that pattern, which only occurs twice in Netherlands.  That second occurrence is replaced with 'xy' and all the other countries are just copied.

Replace Text, All

A simple "replace all" operation.  Given a text source field find all occurrences of the Search for text, replace all such occurrences with the Replace with text and save the result into the target field.   If no occurrences of the Search for text are found, copy the source field into the target field.

 

Example: Put into Temp_text the Replace Text, All result using Country, a regexp Search for text 'Netherlands' and a Replace with text of 'The Netherlands'.

il_trans_replace_text_all_text.png

 

 

Example: Put into Temp_text the Replace Text, All result using Country, a regexp Search for text 'lands' and a Replace with text of

 

CAST(DateTimeYear(#11/16/2016#) AS NVARCHAR)

 

il_trans_replace_text_all2_text.png

This example shows how an SQL expression can be used within a combo box for the the template.   Instead of providing a literal text string we use the DateTimeYear(<date>) SQL function to extract the year from the specified date.   In SQL dates are specified within grid # characters.   The result must be a text sting so we do a CAST to turn the numeric result of the function into an nvarchar string.

 

Example: Put into Temp_text the Replace Text, All result using Country, a regexp Search for text 'lands' and a Replace with text of

 

CAST(DateTimeDay([Date]) AS NVARCHAR)

 

il_trans_replace_text_all3_text.png

When we write an SQL expression into a combo box we do not have to use literals only.   We can refer to other fields within square [ ] brackets.  This example shows how we use a Date field in the table as the source of the date from which we extract the day of month using Manifold's DateTimeDay(<date>) SQL function.

 

Of course it is an artificial example to replace characters within the name of a country with parts of a date.   But in a table where we have dates or where there are comments or other fields where we would like to automatically replace some value with a text string created by a simple SQL expression, that is very easy to do as demonstrated above.

 

The Replace Text, All template is basically analogous to the Replace Regexp, All template except that instead of searching for a regular expression pattern it searches for the given text.

Replace Text, All, Intl

Same as Replace Text, All but with optional specification of the language used.

Replace Text, Nth

Given a text source field, a Search for text string, a Replace with text string and an Occurrence number, n, (zero based counting, with the first Occurrence being 0, the second being 1 and so on), find the nth instance of any match to the given Search for text string, replace it with the Replace with text and put the result into the target field.  If no nth instance of the given Search for text string is found, copy the source field into the target. Check the Ignore case box to treat upper case and lower case letters as the same.

 

Example: Put into Temp_text the Replace Text, Nth result using Country, a Search for text of 'e', a Replace with text of '*' and an Occurrence of 1.

il_trans_replace_regexp_nth_text.png

An Occurrence of 1 tells us (zero based counting) to find the second occurrence of the string pattern 'e', that is, the second e character.

 

The Replace Text, Nth template is basically analogous to the Replace Regexp, Nth template except that instead of searching for a regular expression pattern it searches for the given text.

Replace Text, Nth, Intl

Same as Replace Text, Nth but with optional specification of the language used.

Reverse Text

Reverse the text in the source field and put in the target field.

 

Example: Put into Temp_text the Reverse Text result using Country.

il_trans_reverse_text_text.png

Text at End

Copies a group of characters from the end of a source field.   From the end of the source field copy the specified Length of characters and put into the target field.

 

Example: Put into Temp_text the Text at End of Country using a Length of 4.

il_trans_text_at_end_text.png

Text at Start

Copies a group of characters from the start of a source field.  From the start of the source field copy the specified Length of characters and put into the target field.

 

Example: Put into Temp_text the Text at Start of Country using a Length of 4.

il_trans_text_at_start_text.png

Text Cut

Copies a group of characters from within a source field.  For a Start number, n, (counting 0, 1, 2, ...) and a given Length starting at and including the nth character position copy the Length number of characters from the source field and put into the target field.

 

Example: Put into Temp_text the Text Cut result using Country with a Start of 2 and a Length of 4.

il_trans_text_cut_text.png

With zero based counting a Start of 2 means to begin at the third character, inclusive.

Text Cut from

Copies a group of characters from within to the end of a source field.  For a Start number, n, (counting 0, 1, 2, ...) starting at and including the nth character position copy all  characters to the end of the source field and put into the target field.

 

Example: Put into Temp_text the Text Cut result using Country with a Start of 2.

il_trans_text_cut_from_text.png

With zero based counting a Start of 2 means to begin at the third character, inclusive.

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.  Put the result into the target field.

 

Example: Put into Temp_text the Title Case form of notes.

il_trans_title_case_text.png

 

Windows-style Title Case

 

Windows-style "title case" as used in Windows system facilities, which Manifold applies, is different than title case as used in most literary styles in English.   There are two main differences:  First, words already all in capital letters are left all in capital letters.   Second, Windows also will capitalize articles, conjunctions and prepositions such as "of" and "the."    

 

Following are some examples showing Windows-style title case using the StringToTitleCase SQL function in the Command Window:

 

? StringToTitleCase('a walk in the park')

 

Returns:  A Walk In The Park

 

? StringToTitleCase('a WALK in the park')

 

Returns:  A WALK In The Park

 

? StringToTitleCase('a waLK iN tHe pARK')

 

Returns:  A Walk In The Park

 

? StringToTitleCase('A WALK IN THE PARK')

 

Returns:  A WALK IN THE PARK

 

As can be seen from the above, strings that consist of all upper case characters will not be modified. To transform such strings into a form where each word is lower case except for an initial capital letter, first  use the Lower Case  transform template to transform the string into all lower case and then apply the Title Case template..  

Title Case, Intl

Same as Title Case but with optional specification of the language used.

Translate Characters

Translate character by character using a list of characters to be translated and what they should be translated into.  Given a Characters string and a Translate to string, for all characters in the source field convert any character in the nth position of the Characters string into the corresponding nth character in the Translate to string and put into the target field.   If the Characters string is longer than the Translate to string, so that there are some characters in the Characters string which do not have a corresponding character in the Translate to string, discard any characters found in the source string that occur in the Characters string but without a corresponding character in the Translate to string.

 

Suppose, for example, we have a Characters string of 'abcd' and a Translate to string of 'ABC'.   All of the a, b and c characters found in the source string will be translated into upper case A, B and C characters.   But all d characters found in the source string will be discarded because d occurs in the Characters string but does not have any matching character in the Translate to string.

 

Example:  Using a Characters string of 'abcdefg' and a Translate to string of 'ABCDEFG' take the Translate Characters result of notes and put into Temp_text.

il_trans_translate_characters_text.png

 

Example:  Using a Characters string of 'abcdefg' and a Translate to string of 'xxxyyyz' take the Translate Characters result of notes and put into Temp_text.

il_trans_translate_characters2_text.png

There is no requirement for characters to be in lexicographic order.  All that matters is the character position within the Characters string and the Translate to string.  The third character in the Characters string is translated into the third character in the Translate to string.   The next example is equivalent to the above example, but with the same characters in different, but correspondingly equivalent, orders in the Characters and Translate to strings.

 

Example:  Using a Characters string of 'agcedbf' and a Translate to string of 'xzxyyxy' take the Translate Characters result of notes and put into Temp_text.

il_trans_translate_characters3_text.png

Characters in the Translate to string can repeat, so that multiple different characters in the source are translated into the same Translate to character.  If characters repeat in the Characters string only the first occurrence has effect.

 

Example:  Using a Characters string of 'aeiouy' and a Translate to string of '******' take the Translate Characters result of notes and put into Temp_text.

il_trans_translate_characters4_text.png

 

Example:  Using a Characters string of 'abdefgc' and a Translate to string of 'ABDEFG' take the Translate Characters result of notes and put into Temp_text.

il_trans_translate_characters5_text.png

In the above example the Characters string has one more character, the c at the end, than does the Translate to string.   The c is unmatched so any c in the source field text will be discarded when that text is translated and put into the target field.

Trim

Remove the specified Trim string from both the beginning and end of the source field text and copy the result into the target field.  If the Trim string repeats at the beginning or end, remove all such repetitions.   

 

The template launches by default with a Trim string of ' ', that is, a space character.   That facilitates the most common use of Trim, to get rid of extra space characters at the beginning or end of a string.

 

Example: Put into Temp_text the result of Trim applied to Country using the default Trim string of ' '.

il_trans_trim_text.png

It is not so easy to see trailing space characters in a string but we can see extra spaces at the beginning of a string when the text does not line up in the table, as in the third and fourth records above.  In the third record there are two extra space characters in the beginning of the string and in the fifth record there are three extra space characters.   Using a Trim string of ' ', that is, one space character between single ' quote characters, will get rid of all extra spaces both at the beginning and the end of the source string.

 

Example: Put into Temp_text the result of Trim applied to Country using the Trim string of 'ce'.

il_trans_trim2_text.png

We have edited the example table to toss in some extra ce characters at the beginning and the end of strings.   Now by using a Trim string of 'ce' we can see how it trims both at the beginning and the end of strings and also how it trims repeated occurrences of the Trim string.

Trim End

Like Trim, but only from the end of the string.

 

Example: Put into Temp_text the result of Trim End applied to Country using the Trim string of 'ce'.

il_trans_trim_end_text.png

Trim Start

Like Trim, but only from the start of the string.

 

Example: Put into Temp_text the result of Trim Start applied to Country using the Trim string of 'ce'.

il_trans_trim_start_text.png

Unescape

 

Unescape JSON

 

Unescape, Decode Special Characters

 

Upper Case

Convert all characters in the source field to upper case and put into the target field.

 

Example: Put into Temp_text the Upper Case form of Country.

il_trans_upper_case_text.png

Upper Case, Intl

Same as Upper Case but with optional specification of the language used.

 

 

 

See Also

Transform Dialog

 

Transform Options

 

Transform Templates

 

Transform Templates - Drawings

 

Transform Templates - Images

 

Transform Templates - Binary

 

Transform Templates - Boolean

 

Transform Templates - Datetime

 

Transform Templates - Geom

 

Transform Templates - Numeric

 

Transform Templates - Tile

 

Transform: Center and Centroids

 

Transform: Escape Templates

 

Example: Two Drawings from the Same Table - Take a table with a geom field that is visualized by a drawing.  Add a second geom field to the table and create an rtree index on that field so it can be visualized by a drawing.   Copy the first drawing, paste it and adjust the pasted copy so it uses the second geom field. Demonstrate how to use the Transform dialog to show "live" modifications in the second drawing compared to the first drawing.

 

Example: Copy one Column into Another Column with Transform - How to use the Transform dialog to copy the contents of one column in a table into another column, but only for selected records.  Uses the Products table from the Nwind example data set.  

 

Example: Transform Field Values using an Expression in the Transform Dialog -  How the Expressions tab of the Transform Dialog may be used to change the values of fields.   We include an example of changing the price of selected products and using two different Transform dialogs open at the same time for two different table windows.

 

Example: Construct JSON String using Select and Transform - Use the Select and Transform dialogs to manually construct a JSON string using values from other fields in a table. Shows how we can manipulate text to build desired contents in a field.

 

Example: Edit a Drawing with Transform Dialog Templates -  In this example we open a drawing and edit objects in the drawing using the Transform dialog Template tab.   Includes examples of using the Add Component button and also the Edit Query button.

 

Example: Use a Transform Dialog Expression to Create Buffers in a Drawing - Use the Expression tab of the Transform Dialog to create three different sizes of buffers for different lines in a drawing and then automatically create a query which does the same thing.  Includes examples of using the Add Component button and also the Edit Query button.

 

Example: Clip Areas with a Transform Dialog Expression - Use the Expression tab of the Transform dialog to clip areas in a drawing to fit within horizontal bounds.   Includes examples of using the Add Component button and also the Edit Query button.

 

Example: Smooth Lines with a Transform Dialog Expression - Use the Expression tab of the Transform dialog to make lines smoother in a drawing so that longer lines are smoothed more.  Includes examples of using the Edit Query button to show how different queries are created automatically depending on if we want to update a field or to add a new component.

 

Example: Transfer Options and Merge Areas - Using the Merge Areas Transform dialog template, an exploration of the difference between using Copy and Sum for transfer options.