Analytics SAQL Reference

SAQL Overview
SAQL Examples
SAQL Basic Elements
SAQL Operators
SAQL Statements
SAQL Functions
Aggregate Functions
Date Functions
String Functions
Math Functions
Windowing Functions
coalesce()
Query Performance
PDF

Newer Version Available

This content describes an older version of this product. View Latest

String Functions

To perform string operations in a SAQL query, use string functions.
Use string functions to manipulate the contents of strings.

See the coalesce function for information on returning the first non-null item in a list that includes strings. See String Operators for information on operators that you can use with strings.

Note

Functions

This is a list of SAQL string functions.

ends_with(string, suffix)

This function returns true if string ends with suffix, and false otherwise. String comparison is case-sensitive. If any of the parameters are null, then the function returns null. If suffix is an empty string, then the function returns null.

index_of(string, searchStr [,position [, occurence]])

This function returns the index of the specified occurrence of searchStr in string beginning at the specified position. The function returns 0 if searchStr is not found. This function is case-sensitive. If any of the parameters are null, then the function returns null.

The default value of position is 1, which means that the function begins searching at the first character of string. An error results if position is negative or zero.

If present, occurrence is an integer indicating which occurrence within string to search for. The value of occurrence must be positive, and defaults to 1 if omitted. So for example, if there is more than one matching occurrence, and occurence is 2, the index of the second occurrence is returned.

Constant values are supported for position and occurrence, not arbitrary expressions.

If searchStr is an empty string, then the function returns null.

len(string)

This function returns the number of characters in the string.

len returns the length of string in characters. If string is null, then len(string) is also null.

Leading and trailing whitespace characters are included in the length returned.

lower(string)

This function returns string with all characters in lowercase. If string is null, then the result is null. Refer to the note for upper() concerning Unicode case mapping.

ltrim(string,chars)

This function removes the left part of a string up to the specified characters, or removes leading spaces.

ltrim returns the value of string with the initial characters removed up to the first character not in chars.

chars may contain multiple characters. If chars is omitted, leading space characters are removed. If string or chars is null, then the result is null.

number_to_string​(number, number_format)

This function converts a number literal to a string literal.
number_to_string returns a string representation of the number number. The string is formatted as specified by number_format. number_format can specify either one or two parameters:
  • number_to_string​(number, number_format)

    The format specified by number_format is used for both positive and negative numbers.

  • number_to_string​(number, <POSITIVE>;<NEGATIVE>)

    If number is positive, the number format specified by <POSITIVE> is used. If number is negative, the number format specified by <NEGATIVE> is used. Note the semicolon separating the two specified formats.

Functionality supported by number_format includes:
  • 0, #, decimal point(.)
  • Thousands separator (,)
  • Percentages (by postfixing %)
  • Prefix and postfix characters: $, +, -, (, ), :, !, ^,&,’,~,{,}
For example:
Number literal Required string literal Use number_format
1234.56 1234.6 ####.#
8.9 8.900 #.000
.631 0.6 0.#
12 12.0 #.0#
1234.568 1234.57 #.0#
12000 12,000 #,###
12000 12 #,
12200000 12.2 0.0,,
12 00012 00000
0.03457 3.46% #.00%
12.3 $12.30 $#.00;($#.00)
-12.3 ($12.30) $#.00;($#.00)
32 + +;-
-32 - +;-
If either argument is null, or if the conversion fails, null is returned.

replace(string, searchStr, replaceStr)

This function returns string with every occurrence of searchStr replaced by replaceStr. If any of the parameters are null, then the function returns null. If searchStr is an empty string, null is returned. This function is case-sensitive.

rtrim(string,chars)

This function removes the right part of a string back to the specified characters, or removes trailing spaces.

rtrim returns the value of string with the final characters removed back to the first character not in chars.

chars may contain multiple characters. If chars is omitted, trailing space characters are removed. If string or chars is null, then the result is null.

starts_with(string, prefix)

This function returns true if string starts with prefix, and false otherwise. String comparison is case-sensitive. If any of the parameters are null, then the function returns null. If prefix is an empty string, then the function returns null.

string_to_number(string)

Function to convert a string literal to a number literal.

This is the reverse of the number_to_string function. If the conversion fails, null is returned.

substr(string,position[, length])

This function returns a substring starting at a specified position and, optionally, of the specified length.

substr returns length characters of string, beginning at character position position. If length is omitted, then length = len(string), so all characters are returned from position to the end of the string. If any of the parameters are null, then the function returns null.

The first character in string is at position 1. If position is negative then the position is relative to the end of the string. So a position of -1 denotes the last character.

If length is negative, then the function returns null. If position > len (string) or position < -len(string) or position = 0, then the empty string is returned.

trim(string,chars)

This function removes the left and right part of a string up to the specified characters, or removes leading and trailing spaces.

trim returns the value of string with the initial and final characters removed to the first character not in chars.

chars may contain multiple characters. If chars is omitted, leading and trailing space characters are removed. If string or chars is null, then the result is null.

upper(string)

This function returns string with all characters in uppercase. If string is null, then the result is null.

The behavior of the upper() and lower() functions—and the characters affected by them—is determined by the default case mapping of the Unicode standard. The mapping considers each Unicode character in isolation without regard for context or language-specific rules. The example above does not reflect the German language handling of the ß character. A natural-language conversion would produce GROSSE.

Note