Compute Functions - Intergraph Smart Materials - Version 2020 (10.0) - Help - Hexagon

ABS

Returns the absolute value of a number.

Syntax: ABS(number)

Example: ABS(-1) -> Result: 1

ADD_MONTHS

Returns a date plus n months.

Syntax: ADD_MONTHS(date,n)

Example: ADD_MONTHS('15-MAY-14', 3) -> Result: ‘15-AUG-14’

CASE

Has the functionality of an IF-THEN-ELSE statement.

Example:

SELECT table_name,

CASE owner

WHEN 'M_SYS' THEN 'The owner is M_SYS'

WHEN 'BIR' THEN 'The owner is BIR'

ELSE 'The owner is another value'

END

FROM all_tables;

CEIL

Returns the smallest integer value that is greater than or equal to a number.

Syntax: CEIL(number)

Example: CEIL(13.8) -> Result: 14

CHR

Returns the character based on the NUMBER code (the opposite of the ASCII function).

Syntax: CHR(number)

Example: CHR(64) -> Result: @

COALESCE

Returns the first non-null expression in the list. If all expressions evaluate to null, then the COALESCE function will return null.

Syntax: COALESCE(expr1,expr2,…,expr n)

Example:

(attr_char1, 2, 3 are null, attr_char4=’Supplier’, attr_char5=’Y’)

SELECT COALESCE( attr_char1,attr_char2,attr_char3,attr_char4,attr_char5 ) result

FROM m_projects;

Result: Supplier

COS

Returns the cosine of a number.

Syntax: COS(number)

Example: COS(0) -> Result: 1

CURRENT_DATE

Returns the current date in the time zone of the current session.

Example: CURRENT_DATE -> Result: ‘15-Aug-2014 18:37:17’

CURRENT_TIMESTAMP

Returns the current date and time in the time zone of the current session (a TIMESTAMP WITH TIME ZONE value).

Example:

CURRENT_TIMESTAMP -> Result: ‘15-AUG-2014 18:37:17,522000 +01:00’

DECODE

Has the functionality of an IF-THEN-ELSE statement.

DECODE(expression , search , result [, search , result]... [, default])

Example:

SELECT company_code,

DECODE(company_id, 5001, 'Company A',

5002, 'Company B',

5003, 'Company C',

'other company') result

FROM m_companies;

Result: for all companies with company_id>5003 ‘other company’

ELSE

See CASE

END

See Case

EXP

Returns e raised to the nth power, where e = 2.71828183.

Syntax: EXP(n)

Example: EXP(1) -> Result: 2.71828183

GREATEST

Returns the greatest value in a list of expressions.

Syntax: GREATEST(expr1, expr2, ... expr_n)

Example: GREATEST(‘A’,’X’,’B’,’S’,’F’) -> Result: ‘X’

INITCAP

Sets the first character in each word to uppercase and the rest to lowercase.

Syntax: INITCAP(string)

Example: INITCAP(‘my car is a PORSCHE’) -> Result: ‘My Car Is A Porsche’

INSTR

Returns the location of a substring in a string.

Syntax: INSTR(string, substring [, start_position [, nth_appearance ] ] )

Example: INSTR(‘This car is not my car’,’car’,1,2) -> Result: 20 (start position of second occurrence of car)

LAST_DAY

Returns the last day of the month based on a date value.

Syntax: LAST_DAY(date)

Example: LAST_DAY(’01-Apr-2014’) -> Result: ’30-Apr-2014’

LEAST

Returns the smallest value in a list of expressions.

Syntax: LEAST(expr1, expr2, ... expr_n)

Example: LEAST(‘A’,’X’,’B’,’S’,’F’) -> Result: ‘A’

LENGTH

Returns the length of the specified string.

Syntax: LENGTH(string)

Example: Length(‘Example’) -> Result: 7

LOG

Returns the logarithm of n base m.

Syntax: LOG(m, n)

Example: LOG(100,1) -> Result: 0

LOWER

Converts all letters in the specified string to lowercase. If there are characters in the string that are not letters, they are unaffected by this function.

Syntax: LOWER(string)

Example: LOWER(‘My Car is a PORSCHE’) -> Result: ‘my car is a porsche’

LPAD

Pads the left-side of a string with a specific set of characters (when string is not null).

Syntax: LPAD( string, padded_length, [ pad_string ] )

Example: LPAD('This is a test', 20, '-');

Result: '------This is a test'

LTRIM

Removes all specified characters from the left-hand side of a string.

Syntax: LTRIM( string, [ trim_string ] )

Note: If trim_string is omitted, the LTRIM function will remove all leading spaces from string.

Example: LTRIM('------This is a test',’-‘ -> Result: 'This is a test'

MOD

Returns the remainder of m divided by n.

Syntax: MOD(m,n)

Note: The MOD function returns m if n is 0.

Example: MOD(18,5) -> Result: 3

MONTHS_BETWEEN

Returns the number of months between date1 and date2.

Syntax: MONTHS_BETWEEN(date1, date2)

Example: MONTHS_BETWEEN('01-DEC-2014','01-JUN-2014') -> Result: 6

NEXT_DAY

Returns the first weekday that is greater than a date.

Syntax: NEXT_DAY(date, weekday)

Example: NEXT_DAY('06-Dec-2014', 'MONDAY') -> Result: ’08-Dec-2014’

NULL

Returns an empty value.

NVL

Let you substitute a value when a null value is encountered.

Syntax: NVL(string, replace_with)

Example: NVL(attr_type,’n/a’) -> Result: ‘n/a’ if the attr_type is empty (null). Otherwise, the attr_type value.

POWER

Returns m raised to the nth power.

Syntax: POWER(m, n)

Example: POWER(2,5) -> Result: 32

REGEXP_INSTR

Extends the functionality of the INSTR function by letting you search a string for a regular expression pattern. The function evaluates strings using characters as defined by the input character set. It returns an integer indicating the beginning or ending position of the matched substring, depending on the value of the return_option argument. If no match is found, the function returns 0.

Syntax: REGEXP_INSTR (string, pattern [, position [, occurrence

[, return_option [, match_parameter]]]])

Example: REGEXP_INSTR('305 Intergraph Way, Madison, AL ','[^ ]+', 1, 4) -> Result: 20

REGEXP_REPLACE

REGEXP_REPLACE extends the functionality of the REPLACE function by letting you search a string for a regular expression pattern. By default, the function returns string with every occurrence of the regular expression pattern replaced with replace_string. The string returned is in the same character set as source_char.

Syntax: REGEXP_REPLACE(string, pattern [, replace_string [, position [, occurrence [, match_parameter]]]])

Example: REGEXP_REPLACE(‘Germany’, '(.)', '\1 ') -> Result: G e r m a n y

REGEXP_SUBSTR

REGEXP_SUBSTR extends the functionality of the SUBSTR function by letting you search a string for a regular expression pattern. It is also similar to REGEXP_INSTR, but instead of returning the position of the substring, it returns the substring itself. This function is useful if you need the contents of a match string but not its position in the source string.

Syntax: REGEXP_SUBSTR (string, pattern [, position [, occurrence [, match_parameter]]])

Example: REGEXP_SUBSTR('305 Intergraph Way, Madison, AL ', ',[^,]+,') -> Result: , Madison,

REPLACE

Replaces a sequence of characters in a string with another set of characters.

Syntax: REPLACE(string, string_to_replace, [replacement_string])

Example:

REPLACE(‘My car is a Porsche’,’Porsche’,’Ferrari’) -> Result: ‘My car is a Ferrari’

ROUND

Returns a number rounded to a certain number of decimal places.

Syntax: ROUND(number, [decimal_places ])

Example: ROUND(123.456,1) -> Result: 123.5

RPAD

Pads the right-side of a string with a specific set of characters (when string is not null).

Syntax: RPAD( string, padded_length, [ pad_string ] )

Example: RPAD('This is a test', 20, '-');

Result: 'This is a test------'

RTRIM

Removes all specified characters from the right-hand side of a string.

Syntax: RTRIM( string, [ trim_string ] )

Note: If trim_string is omitted, the RTRIM function will remove all trailing spaces from string.

Example: RTRIM('This is a test------',’-‘ -> Result: 'This is a test'

SIGN

Returns a value indicating the sign of a number.

Syntax: SIGN(number)

Note: If number < 0, then sign returns -1. If number = 0, then sign returns 0. If number > 0, then sign returns 1.

Example: SIGN(45.125) -> Result: 1

SIN

Returns the sine of a number.

Syntax: SIN(number)

Example: SIN(0) -> Result: 0

SQRT

Returns the square root of number.

Syntax: SQRT(number)

Example: SQRT(25) -> Result: 5

SUBSTR

Allows you to extract a substring from a string.

Syntax: SUBSTR(string, start_position, [length])

Example: SUBSTR('This is a test', 6, 2) -> Result: 'is'

SYSDATE

Returns the current system date and time on your local database.

Example: SYSDATE -> Result: ‘15-Aug-2014 18:37:17’

SYSTIMESTAMP

Returns the current system date and time (including fractional seconds and time zone) on your local database.

Example: SYSTIMESTAMP -> Result: ‘15-AUG-2014 18:37:17,522000 +01:00’

THEN

See CASE

TO_CHAR

Converts a number or date to a string.

Syntax: TO_CHAR(value, [format_mask], [nls_language])

Example: TO_CHAR(16,’000099’) -> Result: ‘000016’

TO_TIMESTAMP

Converts a string to a timestamp.

Syntax: TO_TIMESTAMP(string, [format_mask] ['nlsparam'])

Example: TO_TIMESTAMP('2014/12/24 12:00:00', 'YYYY/MM/DD HH:MI:SS') -> Result: 24-DEC-2014 12:00:00,000000000

TRANSLATE

Replaces a sequence of characters in a string with another set of characters. However, it replaces a single character at a time.

Syntax: TRANSLATE(string, string_to_replace, replacement_string)

Example: TRANSLATE(‘Pos 1122’,’12’,’ab’) -> Result: ‘Pos aabb’

TRIM

Removes all specified characters either from the beginning or the ending of a string.

Syntax: TRIM([[LEADING|TRAILING|BOTH] trim_character FROM ] string)

Note: LEADING - remove trim_character from the front of string. TRAILING - remove trim_character from the end of string. BOTH - remove trim_character from the front and end of string.

Example: TRIM(LEADING '0' FROM '000123') -> Result: '123'

TRUNC

Returns a number truncated to a certain number of decimal places.

Syntax: TRUNC(number, [decimal_places])

Example: TRUNC(123.456,1) -> Result: 123.4

UPPER

Converts all letters in the specified string to uppercase. If there are characters in the string that are not letters, they are unaffected by this function.

Syntax: UPPER(string)

Example: UPPER(‘My Car is a PORSCHE’) -> Result: ‘MY CAR IS A PORSCHE’

WHEN

See CASE