Transforms

ORA supports several transform functions where you can change a value before inserting it in the output. For general information about transforms, see the Text Templates page.

abbr:tableName

Changes the input value to an abbreviation. Supports several lookup tables.

For example, if a field named State has the value "Rhode Island", the result of [State:abbr:us_states] is "RI".

If the input value is not found on the lookup table, the abbr transform returns an empty value. If you want to insert the field's value when no abbreviation is found, you can use a conditional expression where the abbreviation is inserted, or if there is no abbreviation, the original value is inserted:

<[State:abbr:us_states]|[State]>

Table Names

The contents of the tables listed above are maintained on GitHub. You may review them and suggest changes via this page:
https://github.com/jfcardinal/ORA-Tables

abbrSplit:tableName:separator

Splits the input field into substrings and searches for a substring it can convert to an abbreviation. Supports several lookup tables as listed above under abbr transform. It searches from the end of the value.

This transform is a special combination of the abbr and split transforms. It is mostly intended for use with location fields that may contain several subdivisions. abbrSplit will lookup each part, from last to first, until it finds a part that matches a key in the given table. So, for example, to convert "Boston, Massachusetts, USA" to "MA":

<[Place:abbrSplit:us_states:,]>

The process is as follows:

1. Split the field into parts, "Boston", "Massachusetts", "USA".
2. Search for "USA" in the "us_states" table. No match.
3. Search for "Massachusetts" in the "us_states" table. There is a match and the abbreviation is "MA". Return "MA".

capitalize

Changes the first letter of words in the value to uppercase, and the remaining letters in the words to lowercase.

For example, if a field named City has the value "WILKES-BARRE", the result of [City:capitalize] is "Wilkes-Barre".

Date-Related Transforms

The transforms listed below operate on date values:

• date - Formats a date
• dateAdd - Adds days, months, or years to a date
• dateGtoJ - Converts a Gregorian date to a Julian date
• dateJtoG - Converts a Julian date to a Gregorian date
• dateJNYD - Specifies the Julian New Year's Day for subsequent conversions
• dateSubtract - Subtracts days, months, or years from a date
• dayOfWeek - Returns the day of the week for a Gregorian date

date:f1:f2:f3

Formats a date value according to one of the given format parameters, "f1", "f2", or "f3".

Field Values

The field value must be a date recognized by ORA when it standardizes field values.

Date Format Parameters

Format parameters use keywords to indicate where to insert parts of the date.

f1 is a required parameter. If it is missing, the date value will not be transformed. If the date value has a year, month, and day, ORA uses f1 to format the value.

f2 is an optional parameter. If it is missing, it defaults to the f1 value. If the date value has a year and month, but no day, ORA uses f2 to format the value.

f3 is an optional parameter. If it is missing, it defaults to the f2 value. If the date value has a year, but no month and day, ORA uses f3 to format the value.

Format Parameter Keywords

The format specification may include the following keywords. The keywords are case-sensitive, so you must use the keywords below exactly as they appear. You can combine the keywords in any sequence. Any text in the format that does not match one of the keywords will be included as-is in the result.

dd

Replaced by a two-digit day number in the range 01 to 31. For days less than 10, the result will have a leading zero.

d

Replaced by a one- or two-digit day number. For days less than 10, the result will not have a leading zero.

mm

Replaced by a two-digit month number in the range 01 to 12. For months less than 10, the result will have a leading zero.

m

Replaced by a one- or two-digit month number. For months less than 10, the result will not have a leading zero.

Mmmm

Replaced by a mixed-case month name, "January", "February", etc.

Mmm

Replaced by a mixed-case month abbreviation, "Jan", "Feb", etc.

MMMM

Replaced by an upper-case month name, "JANUARY", "FEBRUARY", etc.

MMM

Replaced by an upper-case month abbreviation, "JAN", "FEB", etc.

mmmm

Replaced by an lower-case month name, "january", "february", etc.

mmm

Replaced by an lower-case month abbreviation, "jan", "feb", etc.

yyyy

Replaced by a four-digit year. For years less than 1000, the result will have a leading zero.

yyy

Replaced by a three- or four-digit year. For years less than 1000, the result will not have a leading zero.

Examples

1. Format a date in year-month-day format where all the parts are digits:

   [Birth Date:date:yyyy-mm-dd]

   If a field named Birth Date has the value "12 May 1952", the result is "1952-05-12".

   If a field named Birth Date has the value "May 1952", the result is "1952-05-00".

   If a field named Birth Date has the value "1952", the result is "1952-00-00".

2. Format a date showing only the date parts that are present in the field value:

   [Death Date:date:yyyy-mm-dd:yyyy-mm:yyyy]

   If a field named Death Date has the value "1977", the result is "1977".

3. Format a date in "month day, year" format where the month is a mixed-case month name, not a number:

   [Death Date:date:Mmmm d, yyy:Mmmm, yyy:yyy]

   If a field named Death Date has the value "14 December 1977", the result is "December 14, 1977"".

   If a field named Death Date has the value "December 1977", the result is "December, 1977"".

   If a field named Death Date has the value "1977", the result is "1977".

Special Characters

If you want to use one of the keyword characters (d, m, M, or Y) in the format as a literal character, you must escape it with '\'. For example:

[Birth Date:date:\day\: d, \month\: m, \year\: yyy]

Note that ":" is the parameter delimiter in transforms and it must be escaped in addition to the date formatting keyword characters.

Calendar Warning

Please see the Date Manipulation Notes page for important information about date field values.

dateAdd:delta

Adds days, months, or years to the current value.

The input value must be a date with year, month, and day parts and must not include any modifier word such as "before", etc. The delta parameter must be one or more numbers followed by a suffix, "d" for days, "m" for months, or "y" for years. If a number has no suffix, "d" (days) is assumed.

The result is a date and it is formatted according to the current date format.

For example, if the "Date" field has the value 12 May 1953, the result of [Date:dateAdd:1d] is "13 May 1953".

You may specify multiple numbers separated by a space.

For example, if the "Date" field has the value 12 May 1953, the result of [Date:dateAdd:1y 1d] is "13 May 1954".

When you specify multiple numbers, the changes are applied in the same sequence as the numbers in the parameter.

When a number is negative, the result is the same as subtracting the number of days, months, or years.

For example, if the "Date" field has the value 12 May 1953, the result of [Date:dateAdd:-3y] is "12 May 1950".

See the dateSubtract transform for another way to subtract values from a date.

Calendar Warning

Please see the Date Manipulation Notes page for important information about date field values.

dateAdd treats all dates as belonging to the Gregorian calendar.

When operating on Julian dates, the result will be correct except:

1. when the interval spans the conversion from the Julian calendar to the Gregorian calendar,
2. when the interval spans a year boundary and the starting day of the year is not January 1.

In the following examples, the Julian New Year's Day property is assumed to be March 25.

For example, in British North America 2 September 1752 (Julian) was followed by 14 September 1752 (Gregorian):

Also, in British North America before the change to the Gregorian calendar, the civil year changed on March 25:

dateGtoJ

Given a date using the Gregorian calendar, dateGtoJ converts the date to the Julian calendar.

The input value must be a date with year, month, and day parts and must not include any modifier word such as "before", etc.

For example, if a field named Date has the value "15 OCT 1582", the result of [Date:dateGtoJ] is "5 OCT 1582".

Calendar Warning

Please see the Date Manipulation Notes page for important information about date field values.

ORA does not detect whether a date is a Gregorian date or Julian date. It's up to the user to decide based on the collection, time-period, etc.

Many locales did not use January 1 as the start of a new year when using the Julian calendar. When ORA converts the value, it uses the Julian New Year's Day property to determine the first day of the new year. If the Julian New Year's Day property is not correct for the locale of the current record, the dateGtoJ result will not be correct for some days of the year. See dateJNYD.

dateJtoG

Given a date using the Julian calendar, dateJtoG converts the date to the Gregorian calendar.

The input value must be a date with year, month, and day parts and must not include any modifier word such as "before", etc.

For example, if a field named Date has the value "5 OCT 1582", the result of [Date:dateJtoG] is "15 OCT 1582".

Calendar Warning

Please see the Date Manipulation Notes page for important information about date field values.

ORA does not detect whether a date is a Gregorian date or Julian date. It's up to the user to decide based on the collection, time-period, etc.

Many locales did not use January 1 as the start of a new year when using the Julian calendar. When ORA converts the value, it uses the Julian New Year's Day property to determine the first day of the new year. If the Julian New Year's Day property is not correct for the locale of the current record, the dateJtoG result will not be correct for some days of the year. See dateJNYD.

dateJNYD:first-month:first-day

The dateJNYD transform does not modify the input value. It sets a temporary value for the Julian New Year's Day property. The temporary new year's day value influences subsequent date conversions in the same template.

If the value passed to the transform is not empty, and if the parameters are valid, the dateJNYD transform returns an empty value but produces a true result.

If the value passed to the transform is empty, or if the parameters are not valid, the dateJNYD transform returns an empty value and produces a false result.

The field value passed to the transform does not have to be a date. The value is not processed by the transform. It is only required because the transform infrastructure requires a field and the value must not be empty because no transforms are processed if the initial field value is empty.

Example

Several countries shifted the first day of the new year to January 1 before adopting the Gregorian calendar. For example, Spain changed the new year date to January 1 as of January 1, 1556 and changed to the Gregorian calendar in October, 1582, with its last Julian date being October 4, 1582. To convert a Julian date in the range "1 JAN 1556" through "4 OCT 1882" to a Gregorian date, the Julian New Year's Date must be set to January 1. If your Julian New Year's Day preference is set to a different date, the dateJtoG transform will not return the correct value.

For this example, assume that the Julian New Year's Day preference is set to "March 25". Also assume that date was recorded in Spain.

Calendar Warning

Please see the Date Manipulation Notes page for important information about date field values.

dateSubtract:delta

Subtracts days, months, or years from the current value.

The input value must be a date with year, month, and day parts and must not include any modifier word such as "before", etc. The delta parameter must be one or more numbers followed by a suffix, "d" for days, "m" for months, or "y" for years. If a number has no suffix, "d" (days) is assumed.

The result is a date and it is formatted according to the current date format.

For example, if the "Date" field has the value 12 May 1953, the result of [Date:dateSubtract:1d] is "11 May 1953".

You may specify multiple numbers separated by a space.

For example, if the "Date" field has the value 12 May 1953, the result of [Date:dateSubtract:1y 1d] is "11 May 1953".

When you specify multiple numbers, the changes are applied in the same sequence as the numbers in the parameter.

When a number is negative, the result is the same as adding the number of days, months, or years.

For example, if the "Date" field has the value 12 May 1953, the result of [Date:dateSubtract:-3y] is "12 May 1956".

See the dateAdd transform for another way to add values to a date.

Calendar Warning

Please see the Date Manipulation Notes page for important information about date field values.

dateSubtract treats all dates as belonging to the Gregorian calendar. Please see the dateAdd transform for a discussion of the issues related to calculating dates that span the Julian-to-Gregorian calendar change.

dayOfWeek:version

Returns the day of the week for a Gregorian date. The current value must be a date with year, month, and day parts and must not include any modifier word such as "before", etc.

• If version is "short", "s", or not specified, the result will be an abbreviated day name, "Sun", "Mon", "Tue", etc.

  For example, if the "Date" field has the value 12 May 1953, the result of [Date:dayofweek] is "Tue". The result of [Date:dayofweek:s] is also "Tue"

• If version is "long" or "l", the result will be the full day name, "Sunday", "Monday", "Tuesday", etc.

  For example, if the "Date" field has the value 12 May 1953, the result of [Date:dayofweek:l] is "Tuesday".

  The actual short and long day names and abbreviations are determined by your browser's current locale settings.

• If version is "number" or "n", the result will be the day of the week number, as a digit, where Sunday is 0 (zero), Monday is 1 (one), etc.

  For example, if the "Date" field has the value 12 May 1953, the result of [Date:dayofweek:n] is "2".

Calendar Warning

dayOfWeek treats all dates as belonging to the Gregorian calendar. The result will NOT be correct for Julian dates.

For example, the Julian date 2 September 1752 was a Wednesday. dayOfWeek will return Saturday, which is correct for a Gregorian calendar date, but incorrect for a Julian calendar date. You may determine the day of week for a Julian date by converting the date to Gregorian first.

For example, if the "Date" field has the value 2 SEP 1752, the result of [Date:dateJtoG:dayofweek:long] is "Wednesday".

Date Range

ORA's date manipulation transforms support dates from 1 January 0001 to dates well into the future. ORA's date manipulation transforms do not support BCE (BC) dates.

extract:pattern:flags

Extracts a substring from the value using a Regular Expression to identify the substring. The pattern is specified as parameter one, and the flags for the Regular Expression are specified as an optional parameter two. If the flags are not provided, they default to "gi".

You must include one capturing group in the Regular Expression to indicate which text to extract.

For example, if the "Name" field has the value "John 'Jack' Smith", the result of [Name:extract:'([^']+)'] is "Jack".

Regular Expression details:

• The initial ' (single quote) matches a single quote.
• The ( starts the capture group. The characters that match the expression within the parentheses will be extracted.
• The [^']+ expression matches one or more characters that are not single-quotes.
• The ) ends the capture group.
• The final ' (single quote) matches a single quote.

There are many resources online for learning about Regular Expressions including RegexOne. There are also many online Regular Expression testers which will help you understand Regular Expressions. I often use regular expressions 101 to test Regular Expressions. On that site, make sure you set the "Flavor" to "ECMAScript (JavaScript)".

extractIndex:pattern:index:flags

Specifying an index number parameter is the main reason to use extractIndex rather than extract.

Extracts a substring from the value using a Regular Expression to identify the substring. The pattern may match the value multiple times, and if so, you may specify an index number to indicate which matching value you want to extract.

The pattern is specified as parameter one, the index is an optional parameter two, and the flags for the Regular Expression are specified as an optional parameter three. If the flags are not provided, they default to "gi". The "g" (global) flag is required and if you do not specify it, ORA will add it.

You must include one capturing group in the Regular Expression to indicate which text to extract.

If index is a positive number, it indicates the index number of the desired match where the first match is 1, the second match is 2, etc.

if index is a negative number, it indicates the index number of the desired match where the last match is -1, the second-to-last is -2, etc.

Examples

• If a field named Place has the value "Hessen (Hesse), Deutschland (Germany)", the result of [Place:extractIndex:\(([^\)]+)\):1] is "Hesse".
• If a field named Place has the value "Hessen (Hesse), Deutschland (Germany)", the result of [Place:extractIndex:\(([^\)]+)\):2] is "Germany".
• If a field named Place has the value "Hessen (Hesse), Deutschland (Germany)", the result of [Place:extractIndex:\(([^\)]+)\):-1] is "Germany".
• If a field named Place has the value "Hessen (Hesse), Deutschland (Germany)", the result of [Place:extractIndex:\(([^\)]+)\):-2] is "Hesse".

The examples above all use the same Regular Expression pattern, \(([^)]+)\), explained as follows:

• \( matches a left-parenthesis. The left-parenthesis must be escaped with a back-slash because a left-parenthesis is a reserved character in Regular Expressions.
• ( starts the capture group. The characters that match the expression within the parentheses will be extracted.
• [^\)]+ is a character class expression that matches one or more characters that are not a right-parenthesis. The back-slash escapes the special meaning of the right-parenthesis within the character class expression.
• ) ends the capture group.
• \) matches a right-parenthesis. The right-parenthesis must be escaped with a back-slash because a right-parenthesis is a reserved character in Regular Expressions.

The Regular Expression above is useful for extracting text that is enclosed in parentheses.

There are many resources online for learning about Regular Expressions including RegexOne. There are also many online Regular Expression testers which will help you understand Regular Expressions. I often use regular expressions 101 to test Regular Expressions. On that site, make sure you set the "Flavor" to "ECMAScript (JavaScript)".

full:tableName

Changes the input value from an abbreviation to the full value. Supports several lookup tables.

For example, if a field named State has the value "RI", the result of [State:full:us_states] is "Rhode Island".

fullSplit:tableName:separator

Splits the input field into substrings and searches for a substring it can convert from an abbreviation to a full value. Supports several lookup tables as listed above under the abbr transform. It searches from the end of the value.

This transform is a special combination of the full and split transforms. It is mostly intended for use with location fields that may contain several subdivisions. fullSplit will lookup each part, from last to first, until it finds a part that matches a key in the given table. So, for example, to convert "Boston, MA, USA" to "Massachusetts":

<[Place:fullSplit:us_states:,]>

The process is as follows:

1. Split the field into parts, "Boston", "MA", "USA".
2. Search for "USA" in the "us_states" table. No match.
3. Search for "MA" in the "us_states" table. There is a match and the full value is "Massachusetts". Return "Massachusetts".

initialCapital

Changes the first letter of the first word in the value to uppercase, and the remaining letters in the value to lowercase.

For example, if a field named City has the value "WILKES-BARRE", the result of [City:initialCapital] is "Wilkes-barre".

lookup:tableName

Changes the input value from its initial value to a replacement value found in the given table. Supports the table(s) listed below.

For example, if a field named Source.Year has the value "1940", the result of [Source.Year:lookup:us_federal_year_to_nara_series] is "T627".

If the specified table returns a date value, the value is formatted according to the user's Date Format preference.

Table Names

[Source.Year:lookup:ca_census_year_to_date]

[Source.Year:lookup:ca_census_year_to_date:date:Mmmm d, yyyy]

[Source.Year:lookup:uk_census_year_to_date]

[Source.Year:lookup:uk_census_year_to_date:date:Mmmm d, yyyy]

[Source.Year:lookup:us_census_year_to_date]

[Source.Year:lookup:us_census_year_to_date:date:Mmmm d, yyyy]

[Source.Year:lookup:us_federal_year_to_nara_series]

The contents of the tables listed above are maintained on GitHub. You may review them and suggest changes via this page:
https://github.com/jfcardinal/ORA-Tables

lookupSplit:tableName:separator

Splits the input field into substrings and searches for a substring it can convert to a replacement value. Supports several lookup tables as listed above under the lookup transform. It searches from the end of the value.

This transform is a special combination of the lookup and split transforms. lookupSplit will lookup each part, from last to first, until it finds a part that matches a key in the given table.

For example, if a field named Source.Census has the value "1940 Census", the result of [Source.Year:lookupSplit:us_federal_year_to_nara_series: ] is "T627".

The process is as follows:

1. Split the field into parts spearated by the space character. The result is "1940", "Census".
2. Search for "Census" in the "us_federal_year_to_nara_series" table. No match.
3. Search for "1940" in the "us_federal_year_to_nara_series" table. There is a match and the replacement value is "T627". Return "T627".

lowercase

Changes the letters in the value to lowercase.

For example, if a field named City has the value "WILKES-BARRE", the result of [City:lowercase] is "wilkes-barre".

Name Part Transforms

The transforms whose names begin with "name" return a name part from a full name. They share a name parser that splits the names into parts using punctuation characters and short lists of common values in each part category. For example, when "Mr." or "Mrs." appears at the start of a full name, the name parser considers them name prefixes.

The name part transforms are:

• namePrefix
• nameGiven
• nameSurname
• nameSuffix

These transforms are designed to work with names in "given name first" format ("John Smith"), as opposed to "surname first" format ("Smith, John"). If a collection has names in "surname first" sequence, you can use the nameToGivenFirst transform to convert the name to "given first" format, then use one of the transforms above to get the part of interest. For example, [Name:nameToGivenFirst:nameGiven].

The transforms expect suffixes to follow the first comma in the name text, for example "John Smith, Jr.". See the description of each transform for other details and/or limitations.

The name part transforms return a standardized version of each part. You may access the non-standardized value via a version of the name part transform that ends with the suffix "Raw". So, namePrefix returns the standardized prefix of the name, namePrefixRaw returns the non-standardized prefix of the name.

Example

Given the name "Prof Abraham Van Helsing, MD":

ORA's name parser will not produce perfect results in all cases. Names are ambiguous and parsing them based on rules cannot account for all the ambiguities. If you need a name parsed into parts, the name part transforms will help and will return the proper results in a majority of instances. However, it is inevitable that the results will not be correct in some instances. Review the results before accepting them into your genealogy datebase.

You may edit the name parser's lists of values using the [Name Prefixes...], [Name PreSurnames...], and [Name Suffixes...] buttons on the OraSettings page.

namePrefix
namePrefixRaw

The namePrefix transform returns the prefix of a name. The prefix is the honorific or title that appears before the given name, if any.

Prefix values include honorifics like "Mr." and "Mrs." and professional titles like "Dr.". ORA will only recognize prefixes that occur in the Name Prefixes list. The default entries in that list include mostly abbreviated words, for example, "Dr." is included but "Doctor" is not. You can view and edit the list via the [Name Prefixes...] button on the OraSettings page.

If a value in the name prefixes list includes any characters inside brackets [ and ], that character is optional and will only be included if Optional Name Part Characters is checked. The optional characters are usually not desired by users from the UK where the usual abbreviation of Mister is "Mr" (no ending period) and not "Mr.". The version with a period is the convention in the USA and some other locations.

The namePrefix transform may alter the case or punctuation of a prefix value. For example, the default entry in the name prefixes list changes "mr" to "Mr.". The namePrefixRaw transform returns the input value unchanged.

Examples

If a field named Name has the value "Mr John Smith, Jr.", the result of [Name:namePrefix] is "Mr." with a period, though the period is subject to the Optional Name Part Characters setting.

If a field named Name has the value "Mr John Smith, Jr.", the result of [Name:namePrefixRaw] is "Mr" without a period.

nameGiven
nameGivenRaw

The nameGiven transform returns the givenname part of a name. The given name begins after the name prefix, if any, and ends before the surname.

The nameGivenRaw transform returns the same value as the nameGiven transform. It is provided for consistency with the other name part transforms.

Examples

If a field named Name has the value "Mr. John Smith, Jr.", the result of [Name:nameGiven] is "John".

If a field named Name has the value "Mr. John Smith, Jr.", the result of [Name:nameGivenRaw] is "John".

nameSurname
nameSurnameRaw

The nameSurname transform returns the surname part of a name. The surname is usually the last word in the full name unless the name includes a comma. When there is a comma, the surname is the last word before the comma. (Text after the comma is the name suffix.)

The surname value may include other words that occur before the surname if those words are in the Name PreSurnames list. You can view and edit the list via the [Name PreSurnames...] button on the OraSettings page.

The default presurnames list includes both "Mac" and "Mc". Those values are only detected as presurnames when they are separated from the following word by a space. So, for example, nameSurname will transform "mac Neil"" into "Mac Neil"", but it will not change "macNeil" into "MacNeil".

The nameSurname transform may alter the case of a presurname value. For example, the default values in the presurnames list change "Van" to "van". The nameSurnameRaw transform returns the input value unchanged.

Examples

If a field named Name has the value "Mr John Smith, Jr.", the result of [Name:nameSurname] is "Smith".

If a field named Name has the value "Abraham Van Helsing", the result of [Name:nameSurname] is "van Helsing".

If a field named Name has the value "Abraham Van Helsing", the result of [Name:nameSurnameRaw] is "Van Helsing".

nameSuffix
nameSuffixRaw

The nameSuffix transform returns the suffix of a name. The suffix is any value following the first comma in the full name value. ORA will also detect suffixes at the end of the name of the values are on the list of common suffixes. Those rules will work for many names, especially common English names, but it will not work well for royal names and names from non-English cultures.

Suffix values include "Jr." and "Sr." and professional suffixes like "M.D.". ORA will treat any word after a comma in the name as part of the suffix. If a value in the suffix also occurs in the Name Suffixes list, ORA will replace the value in the name with the value from the list. The default entries in the list include mostly abbreviated words, for example, "Jr." and "Jnr." are included but "Junior" is not. You can view and edit the list via the [Name Suffixes...] button on the OraSettings page.

If a value in the name suffixes list includes any characters inside brackets [ and ], those characters are optional and will only be included if Optional Name Part Characters is checked. The optional characters are usually not desired by users from the UK where the usual abbreviation of Junior is "Jr" (no ending period) and not "Jr." (with ending period). The version with an ending period is the convention in the USA and some other locations.

The nameSuffix transform may alter the case or punctuation of a suffix value. For example, the default entry in the name prefixes list changes "jr" to "Jr.". The nameSuffixRaw transform returns the input value unchanged.

Examples

If a field named Name has the value "Mr. John Smith, Jr", the result of [Name:nameSuffix] is "Jr." with a period, though the period is subject to the Optional Name Part Characters setting.

If a field named Name has the value "Mr. John Smith, Jr", the result of [Name:nameSuffixRaw] is "Jr" without a period.

nameToGivenFirst

For names in "surname first" format, transforms the name into a "given name first" format. An example of a "surname first" name value is "Smith, John".

This transform expects the given name to follow the first comma in the name value and will not produce the desired result if no comma is present or if a comma is part of the surname, etc.

Examples

If a field named Name has the value "Smith, John", the result of [Name:nameToGivenFirst] is "John Smith".

If a "surname first" name includes a suffix after the given name, such as "Smith, John, Jr.", the suffix will be placed last in the output of nameToGivenFirst. If a field named Name has the value "Smith, John, Jr.", the result of [Name:nameToGivenFirst] is "John Smith, Jr.".

You may chain nameToGivenFirst with the Name Part Transforms to access parts of the name. If a field named Name has the value "Smith, John", the result of [Name:nameToGivenFirst:nameGiven] is "John".

numberToWords

For numeric values less than 999,999, transforms the value from digits to words. For numeric values greater than 999,999, leaves the value as-is. For non-numeric values, returns an empty string.

For example, if a field named Age has the value "7", the result of [Age:numberToWords] is "seven".

padLeft:length:padString

Pads the current string with a given string (repeated, if needed) so that the resulting string reaches a given length. The padding is applied from the left (start) of the current string.

The first parameter is the desired length. It is required. If the value is already equal to or longer than this length, the value is not changed.

The second parameter is the string to add to the left if needed. It is optional and defaults to a single space.

For example, if a field named Enumeration District has the value "12A", the result of [Enumeration District:padLeft:4:0] is "012A".

padRight:length:padString

Pads the current string with a given string (repeated, if needed) so that the resulting string reaches a given length. The padding is applied to the right (end) of the current string.

The first parameter is the desired length. It is required. If the value is already equal to or longer than this length, the value is not changed.

The second parameter is the string to add to the right if needed. It is optional and defaults to a single space.

For example, if a field named Sheet has the value "B", the result of [Sheet:padRight:4:*] is "B***".

plural:one:many:zero

Returns one of three text values based on the numeric value of the given variable. This transform expects up to three options to specify the text values. The first is for a value of 1 or -1, the second is for a value that is not -1, zero, or 1, and the last is for zero. The general form is:

[field-name:plural:one:many:zero]

For example, if a field named Children has the value "2", the result of [Children:plural:one:more than one:zero] is "more than one".

To add an "s" to a word, you could use [Children:plural::s:s].

replace:pattern:newText:flags
replace:oldText:newText:options

Replace has two modes of operation, Regular Expression mode and Literal mode. The default is Regular Expression mode. If you include the option character l (ell), replace operates in Literal mode.

• In Regular Expression mode, replace uses a Regular Expression to identify a substring in the current value and replaces the substring with new text. The pattern is specified as parameter one, newText is optional parameter two, and the flags for the Regular Expression are specified as an optional parameter three. If newText is not provided, it defaults to the empty string. If the flags are not provided, they default to "gi".
• In Literal mode, replace changes oldText to newText in the current value. The oldText is specified as parameter one, newText is parameter two, and the replacement options are specified as parameter three. You may remove oldText by specifying an empty string for newText. See the Literal Mode Options section below for details.

Regular Expression Mode Examples

1. Change underscore to comma and space:

   [Occupation:replace:_:, ]

   If a field named Occupation has the value "farmer_blacksmith", the result is "farmer, blacksmith"".

2. Remove leading zero(es) from a number:

   [Children:replace:^0+]

   If a field named Children has the value "01", the result is "1".

You may use the following special text sequences in the new text:

There are many resources online for learning about Regular Expressions including RegexOne. There are also many online Regular Expression testers which will help you understand Regular Expressions. I often use regular expressions 101 to test Regular Expressions. On that site, make sure you set the "Flavor" to "ECMAScript (JavaScript)".

Literal Mode Examples

1. Change the "quoted exactly" notation from parentheses to brackets:

   [Occupation:replace:(sic):[sic]:l]

   If a field named Occupation has the value "farmr(sic)", the result is "farmr[sic]".

2. Change ", USA" to the empty string:

   [Occupation:replace:, USA::l]

   If a field named Location has the value "Augusta, Maine, USA", the result is "Augusta, Maine"".

Literal Mode Options

These are the Literal mode options.

• You must always specify the l option to enable Literal mode.
• If you specify "l" only, ORA treats that the same as "lgi" and performs a global, case-insensitive replace operation.
• If you specify "lg", ORA will replace all occurrences of oldText that match the uppercase and lowercase characters in oldText exactly. The case-insensitive option is disabled.
• If you specify "li", ORA will replace only the first occurrence of the oldText using a case-insensitive replace operation. The global option is disabled.
• If you specify "l " (an ell and a space), ORA performs a non-global, non-case-sensitive replace operation. Specifying "l " is how you disable both the global and case-sensitive options.

split:separator:index

Splits the field value into parts using the separator to divide the parts and returns the part indicated by the index number.

If index is a positive number, it indicates the index number of the desired part where the first part is 1, the second part is 2, etc.

if index is a negative number, it indicates the index number of the desired part where the last part is -1, the second-to-last is -2, etc.

Examples

1. Return the first part from a field value where the parts are separated by a comma:

   [Place:split:,:1]

   If a field named Place has the value "Boston, Suffolk Country, Massachusetts", the result is "Boston".

2. Return the last part from a field value where the parts are separated by a comma:

   [Place:split:,:-1]

   If a field named Place has the value "Boston, Suffolk Country, Massachusetts", the result is "Massachusetts".

splitCount:separator

Splits the field value into parts using the separator to divide the parts and returns the number of parts.

Example

Return the number of parts in a field value where the parts are separated by a comma:

[Place:splitCount:,]

If a field named Place has the value "Boston, Suffolk Country, Massachusetts", the result is "3".

substring:indexStart:indexEnd

Extracts a subset of characters from a value.

There are several acceptable forms of the parameters. The first parameter, indexStart, is required. The second parameter, indexEnd, is optional. If indexEnd is not specified, it defaults to the end of the value.

Parameter Variations

• If indexStart is a positive number, it indicates the one-origin index of the first character of the desired subset.

  For example, if a field named Test has the value "123456789", the result of [Test:substring:1:1] is "1".

• If indexStart is a negative number, it indicates the one-origin index of the first character of the desired subset relative to the end of the value.

  For example, if a field named Test has the value "123456789", the result of [Test:substring:-1] is "9".

• If indexEnd is a positive number, it indicates the one-origin index of the last character of the desired subset.

  For example, if a field named Test has the value "123456789", the result of [Test:substring:2:3] is "23".

• If indexEnd is a negative number, it indicates the one-origin index of the last character of the desired subset relative to the end of the value.

  Forexample, if a field named Test has the value "123456789", the result of [Test:substring:-3, -2] is "78".

• If indexEnd starts with a plus sign (+), the number that follows indicates the length of the desired subset starting at indexStart.

  For example, if a field named Test has the value "123456789", the result of [Test:substring:3:+2] is "34".

uppercase

Changes the letters in the value to uppercase.

For example, if a field named City has the value "Wilkes-Barre", the result of [City:uppercase] is "WILKES-BARRE".

Common Parameters

Regular Expression Flags

The transforms that accept a Regular Expression as a parameter include an optional parameter to specify flags for the Regular Expression processor.

The default is "gi". If you omit the parameter, ORA will pass "gi" to the Regular Expression processor.

If you want to disable the default "gi" flags, specify one or more spaces as the parameter value.

There are many resources online for learning about Regular Expressions including RegexOne. There are also many online Regular Expression testers which will help you understand Regular Expressions. I often use regular expressions 101 to test Regular Expressions. On that site, make sure you set the "Flavor" to "ECMAScript (JavaScript)".