SQL String Functions

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