1. Home
  2. On-premises 5.2
  3. Guides
  4. Tours
  5. Concepts
  6. Tools
  7. References
    1. Apache Spark
    2. Connectors
    3. Built-in Functions
      1. Aggregation Functions
      2. Analytic Functions
      3. Boolean Functions
      4. Conditional Statements
      5. Conversion Functions
      6. Filter Functions
      7. Miscellaneous Functions
      8. Arithmetic Functions
      9. Date Functions
      10. String Functions
    4. Incorta Connector SDK
    5. Security
    6. Visualizations
    7. Other
  8. Integrations
  9. Data applications
  10. Releases

Built-in Functions → String Functions

The string functions convert, truncate, and search within text strings. Here is a list of supported string functions:

Functions for Finding and Replacing Strings

FunctionSignatureDescription
find()find(string exp1, string exp2)Returns the index value of the first occurrence of a string within another string
findLast()findLast(string exp1, string exp2)Returns the index of the last substring match
subString()substring(string exp, int exp)Returns a substring from a string expression
subString()substring(string exp, int exp, int exp)Returns a substring from a string expression with a specified length
splitPart()splitPart(string value, string delimiters,int findNum)Returns an element of a delimited string
replace()replace(string field, string old, string new)Returns a string with old substrings replaced by new substrings

Functions for Transforming Strings

FunctionSignatureDescription
lower()lower(string exp)Returns its string argument in all lower case
upper()upper(string exp)Returns its string argument in all upper case

Functions for Trimming Whitespace from Strings

FunctionSignatureDescription
lTrim()lTrim(string value)Returns its string argument without any leading white space
rTrim()rTrim(string str)Returns its string argument without any trailing whitespace
trim()trim(string exp)Returns its string argument without leading or trailing whitespace
FunctionSignatureDescription
ifNull()ifNull(exp1, exp2)Returns one of two expressions depending on whether one of the expressions is NULL
length()length(string exp)Returns the length of a string
bin()bin(double exp, string c1, double d1,...,string default))Splits data values based on specified criteria
concat()concat(string exp, string exp, ...)Returns the concatenation of an arbitrary number of string expressions
repeat()repeat(string value, int count)Returns a string repeated several times
reverse()reverse(string value)Returns a reversed string value

Additional Considerations

Backslash support as an escape character

The Engine supports using the backslash mark \ as an escape character that you can use within a string in a formula to treat the next character as literal text. For strings that you enclose in double quotes and include double quotation marks within, you must precede each quotation mark that you want to include in the string with a backslash \. The same applies to single quotation marks within a string enclosed in single quotes. In addition, to include a backslash in a string, precede it with another backslash as an escape character.

For example:

The formula:

concat(
    "Place a string between two double quotes: \"TEXT\"",
    ' or two single quotes: \'TEXT\'.',
    "Use a backslash \\ as an escape character."
)

The Result:

Place a string between two double quotes: "TEXT" or two single quotes: 'TEXT'. Use a backslash \ as an escape character.