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
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.

1substr("CRM", 1, 1) == "C"
2substr("CRM", 1, 2) == "CR"
3substr("CRM", -1, 1) == "M"
4substr("CRM", -2, 2) == "RM"
5substr("CRM", 4, 1) == ""
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.

1len("starfox") == 7
2len(" rocket ") == 8
3len("謝") == 1
4len("") == 0
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.

1ltrim("__c__val__", "_") == "c__val__"
2ltrim(string, " \t\r") == ltrim(string)
3ltrim("aabcd", "ab") == "cd"
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.

1rtrim("__c__val__", "_") == "__c__val"
2rtrim(ltrim(string, " \t\r"),  " \t\r") == trim(string,  " \t\r")
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.

1trim("__c__val__", "_") == "c__val"
2trim("__c__val__", "_c") == "val"
3trim("   c__val   ") == "c__val"
4trim("   c__val   ") == ltrim(rtrim("   c__val   "))
5trim("aaaaaa", "a") == ""
upper(string)

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

1upper("go") == "GO"
2upper ("große") == "GROßE"

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
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.

1lower("JAVA") == "java"