Functions
A function processes a data item or items in an SQL command and returns a value.
function(
operand,...)
The parentheses are optional
if there are no operands to the function.The following table describes the functions available in ObjectServer SQL.
Function | Description | Example |
---|---|---|
array_len(array) |
Returns the number of elements in an array. This function can only be used in procedures or triggers. | If the array myarray has ten elements, array_len(myarray) returns 10 . |
base64_decode(string) |
Decode the base64 encoded string given. | select base64_decode(Location) as Location from alerts.status; |
base64_encode(string) |
Generate a base64 encoded string of the given string. |
|
ceil(real) |
Takes a real argument and returns the smallest integral value not less than the argument. | ceil(2.01) returns 3.000000 |
current_user() |
Takes no arguments and returns the user name of the user that is executing the SQL command. | update alerts.status set Summary = Summary
+ ' modified by ' + current_user; |
current_userid() |
Takes no arguments and returns the user ID of the user that is executing the SQL command | select Node, Summary from alerts.status
where OwnerUID = current_userid() |
dayasnum(time) |
Takes a time argument and extracts the day of the week as an integer. If no argument is specified, the argument is assumed to be the current time. | select dayasnum(LastOccurrence) from mytab; Sunday
is |
dayname(time) |
Takes a time argument and returns the name of the day. If no argument is specified, the argument is assumed to be the current time. | select dayname(LastOccurrence) from mytab; The
output is |
dayofmonth(time) |
Takes a time argument and extracts the day of the month as an integer. If no argument is specified, the argument is assumed to be the current time. | select dayofmonth(LastOccurrence) from mytab; |
dayofweek(time) |
Takes a time argument and extracts the day of the week as an integer. If no argument is specified, the argument is assumed to be the current time. | select dayofweek(LastOccurrence) from mytab; Unlike |
getdate() |
Takes no arguments and returns the current date and time as a Coordinated Universal Time (UTC) value (the number of seconds since 1 January 1970). | To return all rows in the alerts.status table
that are more than ten minutes old:
|
getenv(string) |
Returns the value of the specified environment variable as a string. | getenv('NCHOME') returns a directory name,
for example, /opt/netcool . |
get_prop_value(string) |
Returns the value of the specified ObjectServer property as a string. | get_prop_value('Name') returns the ObjectServer
name, for example, NCOMS . |
getservername() |
Takes no arguments and returns the name of the ObjectServer as a string. | select LastOccurrence from alerts.status
where ServerName = getservername(); |
hourofday(time) |
Takes a time argument and extracts the hour of the day as an integer. If no argument is specified, the argument is assumed to be the current time. | select hourofday(LastOccurrence) from mytab; |
instance_of(class, parent_class) |
Returns TRUE if class is
a subclass of parent_class or if they are equal,
using the hierarchy defined in the master.class_membership table.
Otherwise returns FALSE. The variables class and parent_class can both be either string or integer. |
|
is_env_set(string) |
Returns 1 if the specified environment variable is set; 0 otherwise. | When the NCHOME environment variable is set, is_env_set('NCHOME') returns 1 . |
|
Take the set of named properties in the colon separated list of property names, extract them from the source JSON string and apply them to the destination JSON string. This function is intended for use in a de-duplication trigger where there are handles to a prior version and a current version. The examples utilize the function with an update SQL statement. |
|
|
Get the value of the named property from JSON string. |
|
|
Set the named property to the given value in the JSON string. |
|
log_2(real) |
Takes a positive real argument and returns the logarithm to base 2. | log_2(4.0) returns 2.000000 |
lower(string) |
Converts a character string argument into lowercase characters. | lower('LIMA') returns lima |
ltrim(string) |
Removes whitespace from the left of a string. | ltrim(' tree') returns tree . |
md5_hash(string) |
Generate an md5 hash for the given string. |
|
minuteofhour(time) |
Takes a time argument and extracts the minute of the hour as an integer. If no argument is specified, the argument is assumed to be the current time. | select minuteofhour(LastOccurrence) from mytab; |
mod(int1,int2) |
Returns the integer remainder of int1 divided by int2. | mod(12,5) returns 2 |
monthasnum(time) |
Takes a time argument and extracts the month of the year as an integer. If no argument is specified, the argument is assumed to be the current time. | select monthasnum(LastOccurrence) from mytab; January
is |
monthname(time) |
Takes a time argument and returns the name of the month. If no argument is specified, the argument is assumed to be the current time. | select monthname(LastOccurrence) from mytab; The
output is |
monthofyear(time) |
Takes a time argument and extracts the month of the year as an integer. If no argument is specified, the argument is assumed to be the current time. | select monthofyear(LastOccurrence) from mytab; Unlike |
nvp_exists( |
Verifies that a name-value pair exists. Used with extended attributes. |
nvp_exists(ExtendedAttr, 'Region') Returns TRUE if Region exists in the extended attributes as the name of a name-value pair. If the name does not exist, the function will return FALSE. |
nvp_merge(nvpset_a,
nvpset_b) |
Merge the two sets of Name/Value pairs (NVPs) and create a consolidated NVP set. The new
merged NVP set is returned. If a key exists in both sets, the value in set
nvpset_b overrides the existing value in set
nvpset_a . |
|
nvp_remove(nvpset, key) |
Remove the named key from the provided NVP set. The new NVP set is returned. | update alerts.status set ExtendedAttr = nvp_remove(ExtendedAttr,
'name01'); |
string nvp_get(string name_value_pairs, string
key) |
Retrieves the value of name in
a name-value pair. If the name exists in the attribute, the function returns the value. If name_value_pairs is not valid, the empty string is returned and an error logged. If the key is not present, the empty string is returned (''). If there are multiple entries for the name, the first one is returned. |
nvp_get(ExtendedAttr, 'Region') Returns the Region attribute. |
string nvp_set(string name_value_pairs, string
key, any value) |
Replaces or adds a name-value pair to a name-value
pair string. Returns a new name-value pair string with the new name-value
added or replaced. Adds or replaces keys from a name-value pair string and returns the new name-value pair string. A date is stored in seconds since 1970; that is, the UNIX epoch format. If name_value_pairs is not a valid string (that is, it is an empty string or contains entries that do not conform to the correct format ), then $key="$value" is returned and an error is logged. If there are multiple entries for the key, only the first is replaced. |
ExtendedAttr = nvp_set(ExtendedAttr,
'Region', 'EMEA'); Sets the Region attribute in the extended attributes. |
power(real1, real2) |
Takes two real arguments and returns real1 raised to the power of real2. | power(2.0, 3.0) returns 8.000000 |
rtrim(string) |
Removes whitespace from the right of a string. | rtrim('tree ') returns tree . |
secondofminute(time) |
Takes a time argument and extracts the second of the minute as an integer. If no argument is specified, the argument is assumed to be the current time. | select secondofminute(LastOccurrence) from mytab; |
split_multibyte( |
Returns the complete multi-byte string strout of
at most int_chunk_len bytes, starting from byte (int_chunk_no
-1) * int_chunk_len of string message. If the split will cause a multi-byte character to be incomplete in the target string, the function returns the largest complete string it can. The next call to the function (providing the int_chunk_len is the same at the previous call) will start from the character that could not be completely extracted. The function will split a multi-byte string into smaller strings that hold only complete multi-byte characters. This is primarily meant for storing large strings into several smaller database fields. The int_chunk_len in
all calls to the |
See the example that follows this table. |
substr(string_message, |
Extracts a substring, starting at the position specified in the second parameter, for the number of characters specified by the third parameter. The string is indexed from 1. | substr('abcdefg', 2, 3) returns bcd ,
starting at the second character, returning the next 3. |
|
Converts the argument to a string. The argument can be of any data type except a string. The comma (,) is required only if a conversion string is specified. If the argument is a time type, you can specify a second
argument consisting of a conversion specification to format the output.
This format is defined in reference date/time format section. The
default format is |
to_char(73) returns 73
|
|
Converts the argument to an integer. The argument can be of any data type except integer. This function strips any leading white space from the argument, and then scans the remaining string. The scan stops when it encounters a character that cannot be converted to a decimal character, or when it reaches the end of the string, whichever happens first. When the scan stops, the function converts the characters to their decimal value, or returns 0 if it failed to encounter any characters that could be converted to decimal. |
to_int( '73 ') returns 73
|
|
Converts the argument to a 64-bit real number. The argument can be of any data type except real. This function strips any leading white space from the argument, and then scans the remaining string. The scan stops when it encounters a character that cannot be converted to a decimal character, or when it reaches the end of the string, whichever happens first. When the scan stops, the function converts the characters to their decimal value, or returns 0 if it failed to encounter any characters that could be converted to decimal. |
to_real('7.3') returns 7.300000
|
|
Converts the argument to a time type. The argument can be of any data type except a time type. The comma (,) is required only if a conversion string is specified. If the argument is a string type, you can specify a second
argument consisting of a conversion specification to format the output.
The default format is |
update mytab set my_utc_col = to_time('Thu
Dec 11 16:00:00 2003') |
to_unsigned(argument) |
Converts the argument to a 64-bit unsigned integer. The argument
can be of any data type except a 64-bit unsigned integer. This function strips any leading white space from the argument, and then scans the remaining string. The scan stops when it encounters a character that cannot be converted to a decimal character, or when it reaches the end of the string, whichever happens first. When the scan stops, the function converts the characters to their decimal value, or returns 0 if it failed to encounter any characters that could be converted to decimal. |
to_unsigned('73') returns 73
|
upper(string) |
Converts a character string argument into uppercase characters. | upper('Vancouver') returns VANCOUVER |
user_in_group(UID, GID)
|
Returns TRUE if the user is
a member of the given group, otherwise returns FALSE .This function can take one or two arguments. The single argument form uses the user ID (UID) of the user that is executing the SQL command. The two argument form requires a User ID and a group name or group ID (GID). |
select user_in_group('Normal') from
alerts.status returns TRUE if the current
user is a member of the group Normal .
|
uuidgen_v1() |
Generate a version 1 (time based) UUID. |
|
uuidgen_v4() |
Generate a version 4 (random) UUID. |
|
year(time) |
Takes a time argument and extracts the year as an integer. If no argument is specified, the argument is assumed to be the current time. | select year(LastOccurrence) from mytab; |
Example: Usage of split_multi-byte
for each row res_filter in catalog.restrictions where
res_filter.RestrictionName = rf_users.RestrictionName
begin
-- Populate master.profile with the new row.
-- Cut up the filter text into 255 byte chunks
update master.profiles set HasRestriction = 1,
Restrict1 = split_multibyte( res_filter.ConditionText, 1, 255),
Restrict2 = split_multibyte( res_filter.ConditionText, 2, 255),
Restrict3 = split_multibyte( res_filter.ConditionText, 3, 255),
Restrict4 = split_multibyte( res_filter.ConditionText, 4, 255)
Where UID = rf_users.GranteeID;
end;
In this example:
Restrict1 is assigned at most 255 bytes from res.filter.ConditionText from byte 1
Restrict2 is assigned at most 255 bytes from res.filter.ConditionText from byte (2-1) * 255
Restrict1 is assigned at most 255 bytes from res.filter.ConditionText from byte (3-1) * 255
Restrict1 is assigned at most 255 bytes from res.filter.ConditionText from byte (4-1) * 255