Wave Analytics SAQL Reference

SAQL Overview
Enable SAQL Logs in the Browser
SAQL Basic Elements
SAQL Operators
SAQL Statements
SAQL Functions
Aggregate Functions
Date Functions
String Functions
Math Functions
Windowing Functions
coalesce()
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.

While SAQL operators support strings, and the coalesce() function returns the first non-null item in a list including strings, the following table lists SAQL functions specifically for manipulating strings.

Functions

This table lists the SAQL string functions:
Function Description
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.
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.
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