Open topic with navigation

TIBCO ActiveSpaces® 4.8.0

# SQL String Functions

ActiveSpaces includes the following SQL String functions:

Function | Description
--- | ---
|| (concat operator) | Concatenates two strings and returns a single string. The `||` operator joins together the two strings of its operands.
char(X1,X2,...,XN) | Returns a string composed of the characters represented by the Unicode code points specified by X1, X2, and so on.
instr(X,Y) | Finds the first occurrence of string Y in string X. If a match is found, the function returns a Long value, which is the starting position of string Y in string X. Otherwise, it returns 0. If either X or Y is NULL, then the result is NULL, which in SQL means no value is returned. Remember that in SQL, the first character is considered position 1 and not 0. For example:
    SELECT instr(FIRST_NAME, 'jo') FROM t1
If the `FIRST_NAME` is 'john', the function returns the starting position of the string "jo " in the string "john". In this case, the function returns 1.
ltrim(X) | Returns a string formed after removing space characters, if any, from the left of string X.
ltrim(X.Y) | Returns a string formed by removing the characters that appear in Y from the beginning of X. For example,
    SELECT ltrim(FIRST_NAME, 'j') FROM t1
In this case the function returns "ohn" since it removes the 'j' character from the left of the string.
rtrim(X) | Returns a string formed after removing space characters, if any, from the right of string X.
rtrim(X.Y) | Returns a string formed by removing the characters that appear in Y from the end of X.
    SELECT rtrim(FIRST_NAME, 'n') FROM t1
In this case the function returns "joh" if the value in the column was "john".
trim(X) | Returns a string formed after removing space characters, if any, from both sides of string X.
trim(X,Y) | Returns a string formed by removing the characters that appear in Y from the beginning and the end of X.
    SELECT trim(FIRST_NAME, 'n') FROM t1
In this case the function returns "atha" if the value in the column was "nathan".
lower(X) | Returns a string formed after converting the characters in string X to lower case.
upper(X) | Returns a string formed after converting the characters in string X to upper case.
length(X) | Returns a long that is the length of the string X. If X is NULL, the function returns NULL.
substr(X,Y) | Returns all characters through the end of the string X starting from position Y. The left-most character of X is at position 1. If Y is negative then the first character of the substring is found by counting from the right rather than the left. Characters indices refer to actual UTF-8 characters. For example:
    substr('Hello World', 7)
Returns "World".
substr(X,Y,Z) | Returns a substring of input string X that begins at the position Y character and is Z characters long. The left-most character of X is at position 1. If Z is negative then the abs(Z) characters preceding the position Y are returned. For example:
    substr('Hello World', 2, 4)
Returns 'ello'.
unicode(X) | Returns the numeric Unicode code point corresponding to the first character of the string X. If X is not a string, the result is undefined.

Copyright © 2009-2022. Cloud Software Group, Inc. All Rights Reserved.