Text Functions

Text functions are available from the Text Function menu. They operate on text properties and are useful for formatting the results of text-related queries. These functions are available for every data provider except for raster, WFS, and WMS providers.

When you create an expression for geospatial features, you can use the following text functions:

Function	Definition	Syntax	Example
CONCAT	Joins multiple strings into one. CONCAT takes any number of arguments, which can be any property type except Geometry or Raster. The return value uses the String data type. Use ‘\n’ to insert a line break when you use standard styles. Use '\P' to insert a line break when you use enhanced styles. These parameters are case-sensitive. To include static text labels or spaces, surround them with single quotes. When you use Concat with a Boolean property, the operation generates 1/0 (not True/False) as a result.	CONCAT(Property, Property, ...)	CONCAT(First_Name, ‘ ‘Last_Name,’\P’ ‘Address: ‘, Street_number, ‘ ‘, Street_name, ‘ ‘, Suffix) This example produces a label like this: John Smith Address: 123 Maple Street
INSTR	Finds the position of the first occurrence of a substring in another string. Specify the source string as the first argument and the string you are searching for as the second argument. Returns an integer with the data type Int64.	INSTR(Text property or value, Text property or value)	INSTR(Parcel_Owner,'field) This example looks for the substring “field” within the parcel owner string. If the name of the parcel owner is Smithfield, the value returned would be 6.
LENGTH	Returns the number of characters of the specified string as an integer with the data type Int64. Does not include trailing blank characters.	LENGTH(Text_property)	LENGTH(First_Name) This example finds the number of characters in the First_Name property.
LOWER	Converts text to lowercase. The return value uses the String data type.	LOWER(Text_property)	LOWER(Product_Name) This example changes a product name entry to lowercase (for example, PIPE COMPOUND to pipe compound).
LPAD	Pads the left side of a string with the characters specified. The return value uses the String data type. LPAD can take two to three parameters. If you do not specify the text character argument, a space character is used.	LPAD(Text_property, Number of characters,'text character')	LPAD(High_Prority,3,'*') This example would show a value for the property High_Priority (for example, “Emergency Repair”) preceded by ***.
LTRIM	Trims characters from the left side of a text string. The return value uses the String data type.	LTRIM(Text_property)	LTRIM(Parcel_ID) This example removes leading characters from parcel identifiers.
RPAD	Pads the right side of a string with the characters specified. The return value uses the String data type. RPAD can take two to three parameters. If you do not specify the text character argument, a space character is used.	RPAD(Text_property, Number of characters,'text character')	RPAD(Costly_Repair,3,$) This example would show a value for the property Costly_Repair (for example, “Complete Redesign”) followed by $$$.
RTRIM	Trims characters from the right side of a text string (trailing characters). The return value uses the String data type. To remove leading characters, use TRIM. See TRIM.	RTRIM(Text_property)	RTRIM(Parcel_ID) This example removes characters that follow parcel identifiers.
SOUNDEX	Returns names that, in English, sound like the specified text string. The return value uses the String data type. The underlying RDBMS evaluates any expression including the SOUNDEX function. However, the result returned by a MySQL provider may differ from the result you receive from other providers.	SOUNDEX(Text_property) = SOUNDEX('text string')	SOUNDEX(Last_Name) = SOUNDEX('Smith') This example finds last names that sound like Smith (for example, “Smythe”).
SUBSTR	Extracts a substring from a string. If the start position is 0, it is treated as 1. If you specify a negative value for this position, the expression counts backwards from the end of the string. The return value uses the String data type. For example, SUBSTR('ABCDEFG',-6,4) finds BCDE.	SUBSTR(Text_property, Starting position, Length of substring)	SUBSTR(Last_Name,0,4)
TRANSLATE	Replaces a sequence of characters with another set of characters. The return value uses the String data type. For example, TRANSLATE('A GIS Specialist''s Guide to C#' ' ''#', '___') transforms the book title shown to “A_GIS_Specialist_s_Guide_to_C_).” The original title required an additional single quote as an escape character.	TRANSLATE(Text_property,'from-text','to-text')	TRANSLATE(Comment_Property ' ''#', '___') This example changes spaces, quotation marks, and # characters to underscore characters.
TRIM	Trims leading characters from a text string. To remove trailing characters, use RTRIM. The return value uses the String data type. You can specify an optional argument: be BOTH, LEADING or TRAILING. If you do not specify the trim character, a blank space is assumed. See RTRIM.	TRIM('optional_argument', Text_property)	TRIM(‘trailing’, Parcel_ID) This example trims trailing characters from the parcel ID.
UPPER	Converts text to uppercase. The return value uses the String data type.	UPPER(Text property)	UPPER(Parcel_Owner) This example would display the value “John McMansion” as “JOHN MCMANSION.”