Open topic with navigation

TIBCO ActiveSpaces® - Enterprise Edition 5.0.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 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 that 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 the 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 the 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.
regexp(<pattern>, <column>) | Returns 1 if the pattern matches the column value and 0 otherwise
regexp_extract(<column>, <pattern>) | Returns the substring that matches the pattern.
regexp_extract(<column>, <pattern>, n) | Returns the nth matching group.
json_extract(X,P1,P2,...) | Returns one or more values from the well-formed JSON string X.

Did you find this helpful?

Yes No
Great!
Thanks for taking the time to give us some feedback.
Sorry about that

Why wasn't this helpful? (check all that apply)

Could not find what I was looking for
Instructions confusing or unclear
Instructions did not work

Want to tell us more?

Please select at least one option or provide details about the issue!!

Error occurred while submitting feedback.

Error2 occurred while submitting feedback.

TIBCO ActiveSpaces® - Enterprise Edition

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