Text Functions

Autodesk Expression Builder

 
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 two strings into one.

CONCAT takes two arguments, which can be any property type except Geometry or Raster properties. The return value uses the String data type.

You must nest any functions you use with this function, because it takes only two parameters.

CONCAT(Property, Property)

CONCAT(First_Name, Last_Name)

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 value or property)

INSTR(Parcel_Owner,'field)

This example looks for the substring “field” within the parcel owner string.

If the parcel owner’s name 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 don’t 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 don’t 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.

Any expression including the SOUNDEX function is evaluated by the underlying RDBMS. 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_).” Note that 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.”