You are here: System Setup > Attributes > Calculated Attributes > Text Functions

Text Functions

Calculated Attribute expressions can be written using the following available functions. Examples follow the Available Functions table.

Use '\n' for producing a new line in a string constant.
All constant text is escaped using the escape character '\'. When writing regular expressions, to get a single '\' from a string constant, use '\\'. This is only true for text constants written in a formula—not when taken from an attribute value.
Constant strings within each other (for example, iterate(list, "prodval('single-back-slash: \\\\')")) require extra levels of escaping—one level of escaping per constant text.

Available Functions

Text Function	Parameters	Description
EXACT	(text1, text2)	Returns TRUE (=1) if 'text1' equals 'text2', otherwise return FALSE (=0).
CONCATENATE	(text1, text2, ...)	Returns the concatenated text of all the supplied texts in the parameter string.
LEFT	(text [, len]):	Returns the first ‘len’ number of characters of the text. [len] is optional, the default is 1.
RIGHT	(text[ , len])	Returns the last ‘len’ number of characters of the text. [len] is optional, default is 1.
MID	(text, pos, len)	Returns the substring of ‘text’ starting at character position ‘pos’ with length ‘len’.
REPLACE	(text, pos, len, newtext)	Returns ‘text’ where the substring starting at character position ‘pos’ with length ‘len’ has been replaced with the text ‘newtext’.
SUBSTITUTE	(text, from, to [, indexno])	Returns ‘text’ where all instances of text ‘from’ are replaced with text ‘to’. [indexno] is optional. If supplied, only that one instance is replaced.
FIND	(searchtext, text [, pos])	Returns first character position in ‘text’ where ‘searchtext’ is found—search will begin at character position ‘pos’ (default is 1). Search is case sensitive. [pos] is optional. If not found, ‘N/A’ is returned.
REGEXPSEARCH	(searchtext, text [, pos])	Returns first character position in 'text', matching the regular expression 'searchtext'. Search will begin at character position 'pos'. Search is case sensitive. [pos] is optional, the default is 1. If not found, ‘N/A’ is returned. Note: \ is used to escape characters in a text constant (see bullets above). For example, to get a regexp string like '\s', use double escaping of the backslash character, like '\\s'.
REGEXPSEARCHI	(searchtext, text [, pos])	Same as function REGEXPSEARCH, but search is case insensitive. [pos] is optional, if not found ‘N/A’ is returned. Note: \ is used to escape characters in a text constant (see bullets above). For example, to get a regexp string like '\s', use double escaping of backslash character like '\\s'.
REGEXPSUBSTITUTE	(text, from, to)	Return 'text' where all instances of text 'from' is replaced with text 'to'. Regular expressions are allowed as defined at http://oracle.com/javase/. Search for 'Pattern (Java Platform SE 7 )'. Note: \ is used to escape characters in a text constant (see bullets above). For example, to get a regexp string like '\s', use double escaping of backslash character like '\\s'.
LEN	(text)	Return number of characters in ‘text’.
TRIM	(text)	Return 'text' where leading and trailing spaces are removed.
UPPER	(text)	Return 'text' where all characters are in upper case.
LOWER	(text)	Return 'text' where all characters are in lower case.
PROPER	(text)	Return 'text' where the first letter of all words in the text string (and the first letter that follows any character other than a letter) is capitalized. Converts all other letters to lowercase letters.
FIRSTNAMEFIRST	(text)	Function for reorganizing names formatted with family name first followed by a comma and then first name.
SOUNDEX	(text)	Return the four-character Soundex encoding of 'text'. The Soundex code for a name consists of a letter followed by three numerical digits: the letter is the first letter of the name, and the digits encode the remaining consonants. Similar-sounding consonants share the same digit. For example, the consonants B, F, P, and V are each encoded as the number 1. Vowels can affect the coding, but are not coded themselves except as the first letter. However, if 'h' or 'w' separate two consonants that have the same Soundex code, the consonant to the right of the vowel is not coded. The correct value can be found as follows: The first letter of the name is the letter of the Soundex code, and is not coded to a number. Replace consonants with digits as follows (after the first letter): b, f, p, v > 1 c, g, j, k, q, s, x, z > 2 d, t > 3 l > 4 m, n > 5 r > 6 h, w are not coded Two adjacent letters with the same number are coded as a single number. Letters with the same number, separated by an 'h' or 'w', are also coded as a single number. Continue until you have one letter and three numbers. If you run out of letters, fill in 0s until there are three numbers.
METAPHONE3	(text)	The function returning the primary Metaphone 3 code. Metaphone 3 has two 'sounds like' strings for a given input string—a 'primary' and an 'alternate'. In most cases they are the same, but for non-English names they can be a bit different, depending on pronunciation. Metaphone 3, like Soundex, is based on the idea of constructing a representative code for an input string. Metaphone 3 is a good choice for person names. Metaphone 3 tries to give name variations or names that sounds like the same code.
METAPHONE3ALTERNATE	(text)	The function returning the alternate Metaphone 3 code.
ESCAPEMERGE	(split-character, escape-character, text1, ...)	Used in text manipulation. Merges text1...textN into a single string. The 'split-character' is used as a separator between the elements. If the 'split-character' is found in the text elements, then the 'escape-character' precedes it. Opposite function of ESACPESPLIT.
ESCAPESPLIT	(split-character, escape-character, text)	Used in text manipulation. Takes a single string and splits it into a list of elements, using the 'split-character' in the string, and removing the 'escape-character' after splitting the elements. To remove duplicates in a string, use ESCAPESPLIT to split a single text to multiple elements. Then use the UNIQUE function to remove the duplicates. Afterward, use LISTCONTATENATE to return the list elements to single string. Opposite function of ESCAPEMERGE.

Examples

Copy and paste any of the following examples into the Function Editor and then evaluate to get the defined result.

exact("hello", "hallo") //= 0

exact("hello", "hello") //= 1

concatenate("hello", "hallo") //= hellohallo

left('hello') //= "h"

left('hello',3) //= "hel"

right('hello') //= "o"

right('hello', 3) //= "llo"

mid('hello',2,3) //= "ell"

replace('hello',2,3,'el') //= "helo"

substitute('hellohellohello', 'ell', 'al') //= "halohalohalo"

substitute('hellohellohello', 'ell', 'al', 2) //= "hellohalohello"

find('llo','hellohello') //= 3

find('llo','hellohello', 4) //= 8

find('lllo','hellohello') //= "N/A"

regexpsearch('e.*he', 'hellohello') //= 2

regexpsearch('[ABC]\\d', 'HELLOHA2MSTER') //= 7

regexpsearchi('[efg]|[ojk]', 'HELLOHELLO') //= 2

regexpsearchi('[abc]|[ojk]', 'HELLOHELLO', 3) //= 5

regexpsubstitute("kat;abe", "(.*);(.*)", "$2$1") //= "abekat"

regexpsubstitute("kat  abe", "(\\w*)\\s*(\\w*)", "$2$1") //=  "abekat"

len('hello') //= 5

trim('   hello   ') //= 'hello'

upper('hello') //= 'HELLO'

lower('HELLO') //= 'hello'

proper("hello, are you john's son?") //= "Hello, Are You John'S Son?"

firstnamefirst("Johnson, John S.") //= "John S. Johnson"

firstnamefirst("Joe Dalton") //= "Joe Dalton"

soundex("hello") //= H400

metaphone3("Joel Dalton") //= JLTLTN

metaphone3alternate("Joel Dalton") // = ALTLTN

escapemerge(';', '/', "Red", "Orange; Blue", "Violet") //= Red;Orange/; Blue;Violet

escapesplit(';', '/', 'Red;Orange/; Blue;Violet') // = RedOrange; BlueViolet

Note: list(listconcatenate(prodval("height"),prodval("width"),prodval("length")),'*') // width value is empty, and this needs to be concatenated without an extra separator '*', for that listconcatenate and list functions are used. IF function can be used to check whether a value is empty or not before concatenating, but it needs lot of code to check for each concatenating value. The above method is easy to write in this scenario.

2017, Stibo Systems