Analytics SAQL Reference
jNewer Version Available
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 is a list of SAQL string functions.
ends_with(string, suffix)
1ends_with("FIT", "T") == true
2ends_with("FIT", "BIT") == falseindex_of(string, searchStr [,position [, occurence]])
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.1index_of("Hawaii", "a") == 2
2index_of("Hawaii", "a", 2) == 2
3index_of("Hawaii", "a", 3) == 4
4index_of("Hawaii", "a", 3, 2) == 0
5index_of("Hawaii", "i", -1, 1) == error
6index_of("Hawaii", "i", -3, 1) == error
7index_of("", "i") == null
8index_of("i", "") == nulllen(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("") == 0lower(string)
1lower("JAVA") == "java"ltrim(string,chars)
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"number_to_string(number, number_format)
- <POSITIVE>;<NEGATIVE> format
- 0, #, decimal point(.)
- Thousands separator (,)
- Percentages (by postfixing %)
- Prefix and postfix characters: $, +, -, (, ), :, !, ^,&,’,~,{,}
| 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 | - | +;- |
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.
1replace("Watson, come quickly.", "quickly", "slowly") == "Watson, come slowly."
2replace("Watson, come quickly.", "o", "a") == "Watsan, came quickly."
3replace("Watson, come quickly.", "", "Mr.") == nullrtrim(string,chars)
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")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.
1starts_with("FIT", "F") == true
2starts_with("FIT", "BIT") == falsestring_to_number(string)
This is the reverse of the number_to_string function. If the conversion fails, null is returned.
substr(string,position[, 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) == ""trim(string,chars)
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)
1upper("go") == "GO"
2upper ("große") == "GROßE"