String Functions and Operators

SQL for CRM Analytics supports the following string functions and operators.

• ascii(char)

  Returns UTF-8 numeric value of the specified character. Returns null if {n} is null.

• chr(int)

  Returns the UTF-8 character for integer n. Returns null if {n} is null.

• char_length(str)

  Returns the number of characters in a given string.

• ends_with(source_str, search_str)

  Returns a Boolean indicating whether a string ends with the search string.

• index_of(source_str, search_str, [position,occurrence])

  Returns a boolean indicating whether a string ends with the search string.

• lower(str)

  Returns a copy of string str with all cased characters converted to lowercase.

• mv_to_string(multivalue_column_name, [delimeter])

  Converts multivalue fields to string fields.

• position(search_str IN source_str)

  Returns an integer that indicates the first occurrence of a substring in a given string. If the substring is not found, the function returns 0.

• replace(str, old_str, new_str)

  Replaces all occurrences in string str of a substring old_str with a new substring new_str. Returns a new string.

• starts_with(source_str, search_str)

  Returns a Boolean indicating whether a string begins with the search string.

• substring(str FROM start FOR length)

  Returns a substring from string str that begins with the character at the start position.

• trim(LEADING | TRAILING | BOTH, chars, str)

  Removes leading or trailing characters from a string. If no characters are specified, the function removes blank spaces.

• upper(str)

  Returns a copy of string str with all cased characters converted to uppercase.