String FunctionsExpression Builder - trillium_discovery - trillium_quality - Latest

Appends multiple values (such as strings, attributes, and integers) together with a space between each.

Syntax

APPEND(value1,value2,value3)

where:

• value1 is the first value in a series of appended argument
• value2 is the second value in a series.
• value3 is the third value, and so on.

Examples

• APPEND("One",Attribute1) returns One Attribute1.
• APPEND("March"," 12,"," 2010") returns March 12 , 2010.

Concatenates two values (such as strings, attributes and integers) without a space. Equivalent to the string operator "II".

Syntax

CONCAT("value1","value2")

where:

• value1 is the first string value to be joined into one string.
• value2 is the second string value to be joined.

Examples

• CONCAT("One","ATX") returns OneATX.
• CONCAT("March"," 12") returns March 12.
• CONCAT(Attribute1,Attribute2) where Attribute 1 is "ABC   ", with three trailing spaces and Attribute 2 is "DEF"returns ABC   DEF

Note: In this last example, Attribute1, "ABC   " is not trimmed. It is concatenated without an additional space to Attribute2 "DEF", with the result "ABC   DEF".

Determines if an attribute contains a specified value, returning TRUE if value is in the attribute and FALSE if the value is not in the attribute.

Syntax

CONTAINS(attribute, "value")

OR

IF (attribute contains "value")

where

• attribute is the value to be checked.
• value is the value searched for in the attribute.

Examples

• CONTAINS(Attribute1,"EE") returns TRUE if EE is contained anywhere in Attribute1.
• IF (String1 CONTAINS TRIM(String2))SET Result1 = "1"ENDIF

Converts a hexadecimal value to a character.

Syntax

HEXVAL("value") || HEXVAL("value")

where value is hexadecimal value to be converted. Adding (||) concatenates the values into a string.

Example

HEXVAL("41") || HEXVAL("41") || HEXVAL("52") || HEXVAL("4F") || HEXVAL("4E") || HEXVAL("0D") || HEXVAL("0A") returns AARON, with a CR/LF at the end.

Note: When using the HEXVAL function, always put quotation marks around input values. To select an attribute, use single quotes (''). To select a text string, use double quotes ("").

Inserts one string into another string at the specified offset.

Syntax

INSSTR(string,"substring",offset,"overwrite")

where

• string is the string value (such as an attribute) into which substring is placed. If string is a literal value, enclose it in double quotation marks ("David Gedge").
• substring is the string value to be placed into string.
• offset specifies where to place substring.
• overwrite specifies if substring will overwrite or insert string, as follows:
  • If overwrite is set to the letter "I" substring is inserted.
  • If overwrite is set to the letter "O" substring overwrites the existing value.
• offset specifies whether the first character of the string should be counted as position 0 or as position 1. Valid values are 0 or 1. The Expression Builder always sets the value as 1 unless the config.txt file was updated to specify 0 or you are you are working with a project created in a legacy (pre-Version 13) project.

Examples

• INSSTR("ABCDEFGHI","123",3,"I") returns "AB123CDEFGHI."
• INSSTR("ADCDEFGHI","123",3,"O") returns "AB123CGHI." Note that DEF is overwritten.

Determines which characters are considered alphabetic. The following characters are valid: a-z, A-Z. You can redefine the alphabetic character set by using a mask table. For example, add commas or spaces to the mask table if you want those characters to be considered alphabetic.

Syntax

ISALPHA(TRIM(LPACK(value)))

where value represents the characters to be validated.

Example

IF ISALPHA(TRIM(LPACK("Charl..."))) SET LINE_02 = "1" ELSE SET LINE_02 = "2" validates that the value Charlie Brown is alphabetic, and if TRUE sets LINE_02 to 1.

Determines which characters are considered alphanumeric. The following characters are valid: a-z, A-Z, 0-9. Returns true (1) if the values are found to contain only numerics. You can redefine the alphanumeric character set by using a mask table. For example, add commas or spaces to the mask table if you want those characters to be considered alphanumeric.

Syntax

ISALPHANUM(TRIM(LPACK(value)))

where value represents the characters to be validated.

Example

IF ISALPHANUM(TRIM(LPACK("35 Cott..."))) SET LINE_04 = "1" ELSE SET LINE_04 = "2" validates that the value 35 Cottonwood Ln is alphanumeric, and if TRUE sets LINE_04 to 1.

Determines which characters are considered numeric. The following characters are valid: 0-9. You can redefine the numeric character set by using a mask table. For example, add commas or spaces to the mask table if you want those characters to be considered numeric.

Syntax

ISNUMERIC(TRIM(LPACK(value)))

where value represents the characters to be validated.

Example

IF ISNUMERIC(LPACK(LINE_06)) SET LINE_07 = "1" ELSE SET LINE_07 = "2" validates LINE_06 as numeric, and if FALSE sets LINE_07 to 2.

Concatenates multiple values such as strings, attributes, and integers. Accepts two or more values.

The "||" is a simple join that concatenates two or more values without trimming any space.

The "|:" is a join with space that trims and the combines two or more values with a space in between.

Syntax

JOIN(value1 || value2)JOIN(value1 |: value2)

where

• value1 is the first value to be concatenated.
• value2 is the second value to be concatenated, and so on.

Example

• JOIN("Twelve" || "blue") returns Twelveblue.
• JOIN(Attribute1 |: Attribute2) where Attribute 1 is "ABC   ", with three trailing spaces and Attribute 2 is "DEF"returns ABC DEF

Note: In this last example, Attribute1, "ABC   " is trimmed to "ABC" and then concatenated with a space to Attribute2 "DEF", with the result "ABC DEF".

Adjusts the contents of an attribute to the left. Leaves a single space between elements and includes any trailing blanks.

Syntax

LADJUST(attribute)

where attribute is the attribute to be adjusted.

If you want the data to be changed, use the following syntax: SET attribute = LADJUST(attribute)

Example

IF A contains "  The cat sat on the mat  " SET B =LADJUST(A). If this example is TRUE, then B contains "The cat sat on the mat    ". In this example, the 2 spaces leading the string is appended to the end of the string to preserve the schema length.

Converts contents of an attribute to lowercase characters.

Syntax

LCASE(value)

where value is the attribute to be converted.

Example

LCASE(INSIGHT) returns insight.

Returns the character length of an attribute.

Syntax

LENGTH(value)

where value is the attribute on which the character length gets returned.

Examples

• LENGTH(Address_1) returns the value 9.
• LENGTH(TEST) returns the value 4.

Left-justifies the contents of an attribute. Removes any leading blanks.

Syntax

LJUST(attribute)

where value represents the content of the attribute to be left-justified.

If you want the data to be changed, use the following syntax: SET attribute = LJUST(attribute)

Example

IF A contains "    Insight  " SET B =LJUST(A). If this example is TRUE, then B contains "Insight  ".

Converts the values of an attribute to lowercase characters.

Syntax

LOWER(value)

where value represents the attribute that will be converted to all lowercase characters.

Example

LOWER(aBcdEFghI) returns abcdefghi.

Moves the contents of a value (such as an attribute) to the left. Removes all spaces between elements and includes any trailing blanks.

Syntax

LPACK(value)

where value represents the value to be moved to the left.

If you want the data to be changed, use the following syntax: SET value = LPACK(value)

Example

IF A contains " The cat sat on the mat " SET B = LPACK(A). If this example is TRUE, then B contains "Thecatsatonthemat       ".

Trims the leading (left-side) white space from attribute values.

Syntax

LTRIM(value)

where value is the attribute on which any leading white space characters is removed. White space characters include spaces, tabs, new lines, carriage returns, and form feeds.

Example

LTRIM ("  Attribute 1") returns "Attribute 1  ".

Parses a string to find the location of a substring and returns a numeric value equivalent to the position of the substring occurrence counted from the left of the string. Returns null (-1) if the string is not found.

Syntax

OCCSTR(string,substring,direction, offset)

where

• string is the string value that is parsed to find the substring location.
• substring is the substring value.
• direction specifies how string is parsed, as follows:
  • If direction is set to "L," then string is parsed from right to left
  • if direction is set to "R," then string is parsed from left to right
• offset specifies whether the first character of the string should be counted as position 0 or as position 1. Valid values are 0 or 1. The Expression Builder always sets the value as 1 unless the config.txt file was updated to specify 0 or you are you are working with a project created in a legacy (pre-Version 13) project.

Note: The OCCSTR function is often used with other functions.

Examples

• Attribute 1 = OCCSTR(Attribute 2,"DEF","R"). If Attribute 2 contains the string "ABCDEFGHI," this operation sets Attribute 1 to 4.
• Attribute 1 = OCCSTR(Attribute 2,"DEF","L"). If Attribute 2 contains the string "ABCDEFGHI," this operation sets Attribute 1 to 4.
• INSSTR(Attribute 2,"123",OCCSTR(Attribute 2,"D","R"),"O"). If Attribute 2 contained the string "ABCDEFGHI," this operation sets Attribute 1 to "ABC123GHI." The OCCSTR function returns 4 and the resulting INSSTR is: Attribute 1 = INSSTR (Attribute 2,"123",4,"O").

Adjusts attribute contents to the right. Leaves a single space between elements and includes any leading blanks.

Syntax

RADJUST(attribute)

where value is the attribute to be adjusted.

If you want the data to be changed, use the following syntax: SET attribute = RADJUST(attribute)

Example

IF A contains "  The cat sat on the ma  t" SET B = RADJUST(A). If this example is TRUE, then B contains "    The cat sat on the mat".

Applies regular expressions to a string and returns the part of the string that matches the expression. If the string returns empty, there is no match.

Syntax

REGEXP("expression","value")

where:

• expression is the expression you want to apply to the string.
• value is the string to which you want to apply the expression.

Examples

• REGEXP(".og","froggy") returns "rog". The period (.) represents one character.
• REGEXP("[^]og",dog") returns an empty string. The [^] instructs the expression to exclude the characters that follow.
• REGEXP("[^d]og[a-z,A-Z]*","smog") returns mog. This instructs the expression to return the portion of the value (smog) where og is preceded by any one character, excluding lowercase d and followed by any number of uppercase and lowercase alpha characters. Using the asterisk (*) implies that there can be 0, 1, or many of the preceding expression.
• REGEXP("(ab|a)c","zabcdefgh") returns "abc".

The following are examples of tests you run using regular expressions:

• To validate email address syntax: REGEXP("[A-Za-z0-9._%-]+@[A-Za-z0-9._%-]+\.[A-Za-z]{2,4}",[Cust Email Addr])<>""
• To test for numbers in the last name: REGEXP("\d",[Last Name])<>""
• To validate special or repeating characters in the first name (. ? - _ *  ^ % # : ; , `): REGEXP("(\.|\[|\\|\$|\(|\)|\?|-|_|\*|\^|%|#|:|;|,|`)+ *$",UPPER([Last Name]))<>""

Returns attribute values in the reverse order.

Syntax

REVERSE(attribute)

where attribute is the attribute value you want reversed.

Examples

• REVERSE("Nelson") returns "nosleN".
• REVERSE("ALBERT JOHNSON") returns "NOSNHOJ TREBLA".

Returns characters from the right of a value based on a supplied number.

Syntax

RIGHT(value,number)

where

• value represents the value from which you want to extract the specified characters.
• number represents the supplied number that determines the characters to extract.

Examples

• RIGHT("ALBERT",3) returns "ERT".
• RIGHT("Nelson Diaz",4) returns "Diaz".
• RIGHT("Nelson Diaz",5) returns " Diaz".

Right-justifies the contents of an attribute. Removes any trailing blanks.

Syntax

RJUST(attribute)

where attribute is the attribute to be right-justified.

If you want the data to be changed, use the following syntax: SET attribute = RJUST(attribute)

Example

IF A contains "    Insight  " SET B =RJUST(A). If this example is TRUE, then B contains "      Insight".

Moves attribute contents to the right. Removes all spaces between elements and includes any leading blanks.

Syntax

RPACK(attribute)

where attribute is the attribute to be moved to the right.

If you want the data to be changed, use the following syntax: SET attribute = RPACK(attribute)

Example

IF A contains " The cat sat on the mat " SET B = RPACK(A). If this example is TRUE, then B contains "       Thecatsatonthemat".

Trims the trailing (right-side) white space from an attribute.

Syntax

RTRIM(attribute)

where attribute is the value on which any trailing white space characters will be removed. White space characters are spaces, tabs, new lines, carriage returns, and form feeds.

Example

RTRIM ("Attribute 1  ") returns "  Attribute 1".

Splits a value relative to a separator character.

Syntax

SPLIT(value,"separator",number)

where:

• value represents the attribute, string, or literal value to be split.
• separator represents the character used as a separator; for example a comma (,), a hyphen (-), vertical line (|), or a space ( ).
• number is the number you supply to determine the part of the split value returned: 
  • 0 returns the part of the value in front of the separator
  • 1 returns the part of the value after the separator
  • 2 returns null

Examples

• SPLIT("Billerica,MA",",",0) returns "Billerica".
• SPLIT("ALBERT|RICHARD","|",1) returns "RICHARD".
• SPLIT("2200011693015","0",1) returns "0011693015".
• SPLIT("ALBERT-RICHARD","-",2) returns "".

Finds and replaces characters in a string.

Syntax

SUBSTITUTE("input_string", "find_string", "replace_string")

where:

• input_string is the target string in which text is replaced.
• find_string is the text to find so that it can be replaced.
• replace_string is the text you want to replace it with.

Example

SUBSTITUTE("substitute test", "te", "xx") returns "substituxx xxst".

Returns a substring which describes characters at the beginning, middle, or end of a string value. This function can also be used in an IF statement.

Syntax

SUBSTR(string,start,length,offset)

where:

• string is the value used to create the substring.
• start is the numeric value used to indicate the position of the character in the string that will become the first character of the substring; for example, if this value is 3 for the string "discount", the substring starts with "s".
• length is the number of characters that you want in the substring.
• offset specifies whether the first character of the string should be counted as position 0 or as position 1. Possible values are 0 or 1. The Expression Builder always sets the value as 1 unless the config.txt file was updated to specify 0. This affects how the start parameter is interpreted. For example, if default is 0, substr("London,England", 7, 7, 0) returns the string "England". However, substr("London,England, 7, 7, 1) will return the string ",Englan".

Note the following guidelines:

• Results are 1-based (offset by 1), unless specified in config.txt file or you are working with a project created in a legacy (pre-Version 13) project, in which case the results are 0-based.
• If string is a 0 (zero) length value, a zero length string value is returned.
• If the start or length values are negative (< 0), then the expression returns the text ERROR.

Examples

• SUBSTR(ABCDEFGHI,3,3) returns the substring "CDE".
• IF (SUBSTR(Attribute 2,3) = "A"). If the third character of Attribute 2 is "A," the IF returns True.
• IF [Date Of Reject] <> "" THEN [Date Of Reject] >= SUBSTR([Referral Date Time],1,8). Validates that if the Date of Reject attribute is not a null value, then the date is greater than or equal to the Referral Date Time substring. The substring starts with the first character in the Referral Date Time attribute and is eight characters in length.

Converts a string to title case (initial cap). When used with the optional argument ANY, converts the first alpha character and all characters immediately following a number to title case (initial cap).

Syntax

TCASE(value, [ANY])

where value is the string to be converted.

Example

TCASE("KEVIN") returns "Kevin".

TCASE("IWANT2TRAVEL", "ANY") returns "Iwant2Travel". (Here, the second 't' is returned uppercase since it is the fist letter following a number.)

TCASE("IWANT2TRAVEL") returns "Iwant2travel". (Here, the second 't' in the string is returned in lowercase.)

Converts multiple numeric values to a single numeric value.

Syntax

TO_NUMBER(value) + TO_NUMBER(value)

where value is the number to be converted.

Example

TO_NUMBER(13.3) + TO_NUMBER(27) returns 40.3.

Removes leading and trailing white space characters (space, tab, new line, carriage return, and form feed) from the start and end of a value.

Syntax

TRIM(value)

where value is the value on which any leading and trailing white space characters are removed.

Example

TRIM( " Attribute 1 " ) returns "Attribute 1".

Converts a value to uppercase.

Syntax

UCASE(value)

where value is the value to be converted.

Example

UCASE("Wendell") returns "WENDELL".