String Manipulation Functions

AMPS provides the SUBSTR function, that can be used for returning a subset of a string. There are two forms of this function.

The first form takes the source string and the position at which to begin the substring. You can use a negative number to count backward from the end of the string. AMPS returns a string that starts at the specified position and goes to the end of the string. If the provided position is before the beginning of the string, AMPS starts at the beginning of the string, returning the full string. If the provided position is past the end of the string, AMPS returns a zero-length string, which evaluates to NULL.

For example, the following expressions are all TRUE:

SUBSTR("fandango", 4) == "dango"

SUBSTR("fandango", 1) == "fandango"

SUBSTR("fandango", -2) == "go"

SUBSTR("fandango", -99) == "fandango"

SUBSTR("fandango", 99) IS NULL

The second form of SUBSTR takes the source string, the position at which to begin the substring, and the length of the substring. Notice that SUBSTR considers the first character in the string to be position 1 (rather than position 0), as demonstrated below. AMPS will not return a string larger than the source string. As with the two-argument form, if the starting position is before the beginning of the string, AMPS starts at the beginning of the string. If the starting position is after the end of the source string, AMPS returns an empty string which evaluates to NULL.

For example, the following expressions are all true:

SUBSTR("fandango", 1, 3) == "fan"

SUBSTR("fandango", -4, 2) == "an"

SUBSTR("fandango", -8, 8) == "fandango"

SUBSTR("fandango", -23, 3) == "fan"

SUBSTR("fandango", 99, 8) IS NULL

AMPS also provides simplified forms of SUBSTR, which simply take the leftmost or rightmost characters from a string. For example, the following expressions all evaluate as true:

LEFT("fandango", 3)  == "fan"

RIGHT("fandango", 2) == "go"

AMPS provides a set of functions that work with whitespace or other delimiter characters. For example, the following expressions are all true:

TRIM(" Lancelot ")  == "Lancelot"

LTRIM(" Lancelot ") == "Lancelot "

RTRIM(" Lancelot ") == " Lancelot"

These functions accept an optional second parameter that specifies the delimiters to remove:

TRIM("=@=Lancelot=@=", "=@") == "Lancelot"

The REVERSE function simply reverses the input string:

REVERSE("fandango") == "ognadnaf"

Function	Parameters	Description
SUBSTR	string to process, starting position, [length]	Returns a portion of the input string, starting at the starting position and ending after the specified length. If the length is not provided, returns the portion of the string from the starting position to the end of the string.
TRIM	string to transform, [characters to trim]	Returns the input string, with all leading and trailing characters in the set of characters to trim removed. The characters to trim parameter is optional. When not provided, the parameter defaults to " " (that is, a space character).
LTRIM	string to transform, [characters to trim]	Returns the input string, with all leading characters in the set of characters to trim removed. The characters to trim parameter is optional. When not provided, the parameter defaults to " " (that is, a space character).
RTRIM	string to process, [characters to trim]	Returns the input string, with all trailing characters in the set of characters to trim removed. The characters to trim parameter is optional. When not provided, the parameter defaults to " " (that is, a space character).
LEFT	string to process, number of characters	Returns the leftmost number of characters from the provided string.
RIGHT	string to process, number of characters	Returns the rightmost number of characters from the provided string.
REVERSE	string to process	Returns the provided string in reverse.