Actian X 11.2 | Scalar Functions

OpenSQL Reference Guide > OpenSQL Reference Guide > Elements of OpenSQL Statements > Operations > Scalar Functions

Was this helpful?

Scalar Functions

There are six types of scalar functions:

• Data type conversion

• Numeric

• String

• Date

• Bitwise

• Random number

• rand() and rand(integer)

The scalar functions require either one or more single-value arguments. In most instances, scalar functions can be nested to any level. Certain restrictions apply when using some Enterprise Access products. For details, see the documentation provided with your Enterprise Access product.

Note: If II_DECIMAL is set to comma, be sure that when OpenSQL syntax requires a comma (such as a list of table columns or OpenSQL functions with several parameters), that the comma is followed by a space.

For example:

Select col1, ifnull(col2, 0), left(col4, 22) from t1:

Data Type Conversion Functions

The following table lists the data type conversion functions. (When converting decimal values to strings, the length of the result depends on the precision and scale of the decimal column.)

Name	Operand Type		Result Type		Description
byte(expr [, len])	any		byte		Converts the expression to byte binary data. If the optional length argument is specified, the function returns the leftmost len bytes. Len must be a positive integer value that does not exceed the length of the expr argument.
c(expr [, len])	any		c		Converts argument to c string. If the optional length argument is specified, the function returns the leftmost len characters. Len must be a positive integer value that does not exceed the length of the expr string.
char(expr [, len])	any		char		Converts argument to char string. If the optional length argument is specified, the function returns the leftmost len characters. Len must be a positive integer value that does not exceed the length of the expr string.
date(expr)	c, text, char, varchar		date		Converts a c, char, varchar or text string to internal date representation.
decimal(expr [,precision[,scale]])	any except date		decimal		Converts any numeric expression to a decimal value. If scale (number of decimal digits) is omitted, the scale of the result is 0. If precision (total number of digits) is omitted, the precision of the result is determined by the data type of the operand, as follows: Operand Default Datatype Precision smallint 5 integer1 5 integer 11 float 15 float4 15 decimal 15 money 15 Decimal overflow occurs if the result contains more digits to the left of the decimal point than the specified or default precision and scale can accommodate.
dow(expr)	date		c		Converts an absolute date into its day of week (for example, 'Mon,' 'Tue'). The result length is 3.
float4(expr)	c, char, varchar, text, float, money, decimal, integer1, smallint, integer		float4		Converts the specified expression to float4.
float8(expr)	c, char, varchar, text, float, money, decimal, integer1, smallint, integer		float		Converts the specified expression to float.
hex(expr)	varchar, char, c, text		varchar		Returns the hexadecimal representation of the argument string. The length of the result is twice the length of the argument, because the hexadecimal equivalent of each character requires two bytes. For example, hex('A') returns '61' (ASCII) or 'C1' (EBCDIC).
int1(expr)	c, char, varchar, text, float, money, decimal, integer1, smallint, integer		integer1		Converts the specified expression to integer1. Decimal and floating-point values are truncated. Numeric overflow will occur if the integer portion of a floating-point or decimal value is too large to be returned in the requested format.
int2(expr)	c, char, varchar, text, float, money, decimal, integer1, smallint, integer		smallint		Converts the specified expression to smallint. Decimal and floating-point values are truncated. Numeric overflow will occur if the integer portion of a floating-point or decimal value is too large to be returned in the requested format.
int4(expr)	c, char, varchar, text, float, money, decimal, integer1, smallint, integer		integer		Converts the specified expression to integer. Decimal and floating-point values are truncated. Numeric overflow will occur if the integer portion of a floating-point or decimal value is too large to be returned in the requested format.
long_byte (expr)	any		long byte		Converts the expression to long byte binary data.
long_varchar (expr)	any		long varchar		Converts the expression to a long varchar.
money(expr)	c, char, varchar, text, float, money, decimal, integer1, smallint, integer		money		Converts the specified expression to internal money representation. Rounds floating-point and decimal values, if necessary.
nchar(expr [, len])		any		nchar		Converts argument to nchar unicode string. If the optional length argument is specified, the function returns the leftmost len characters. Len must be a positive integer value that does not exceed the length of the expr string.
nvarchar(expr [, len])		any		nvarchar		Converts argument to nvarchar unicode string. If the optional length argument is specified, the function returns the leftmost len characters. Len must be a positive integer value that does not exceed the length of the expr string.
long_varchar (expr)	c, char, varchar, text, long varchar, long byte		long varchar		Converts the expression to a long varchar.
object_key(expr)	varchar, char, c, text		object_key		Converts the operand to an object_key.
table_key(expr)	varchar, char, c, text		table_key		Converts the operand to a table_key.
text(expr [, len])	any		text		Converts argument to text string. If the optional length argument is specified, the function returns the leftmost len characters. Len must be a positive integer value that does not exceed the length of the expr string.
unhex(expr)	varchar, c, text		varbyte		Returns the opposite of the hex function. For example, unhex(x'61626320') returns 'abc' and unhex(x'01204161') returns '\001Aa'. Exceptions can occur when a "c" data type suppresses the display of certain stored characters, or when the output data type differs from the input type. Note: The result must be CAST to the desired data type as not all contexts support the display or handling of BYTE and VARBYTE data.
					Typically one character is generated for every two hex digits being converted to a printable character. If the hex digit pair being converted does not translate to a printable character, then the value is converted to a backslash (\), followed by the numeric value of the hex digit pair as a three-digit octal value.
varbyte(expr [, len])	any		byte varying		Converts the expression to byte varying binary data. If the optional length argument is specified, the function returns the leftmost len bytes. Len must be a positive integer value that does not exceed the length of the expr argument.
varchar(expr [, len])	any		varchar		Converts argument to varchar string. If the optional length argument is specified, the function returns the leftmost len characters. Len must be a positive integer value that does not exceed the length of the expr string.

If the optional length parameter is omitted, the length of the result returned by the data type conversion functions c(), char(), varchar(), and text() are as follows:

Data Type or Argument	Result Length
byte	Length of operand
byte varying	Length of operand
c	Length of operand
char	Length of operand
date	25 characters
decimal	Depends on precision and scale of column
float & float4	11 characters; 12 characters on IEEE computers
integer1 (smallint)	6 characters
integer	6 characters
integer4	13 characters
long byte	Length of operand
long varchar	Length of operand
money	20 characters
text	Length of operand
varchar	Length of operand

Numeric Functions

OpenSQL supports the numeric functions listed in the following table:

Name	Operand Type	Result Type	Description
abs(n)	all numeric types and money	same as n	Absolute value of n.
atan(n)	all numeric types	float	Arctangent of n; returns a value from (-pi/2) to pi/2.
cos(n)	all numeric types	float	Cosine of n; returns a value from -1 to 1.
exp(n)	all numeric types and money	float	Exponential of n.
log(n) ln(n)	all numeric types and money	float	Natural logarithm of n.
mod(n,b)	integer, smallint, integer1, decimal	same as b	n modulo b. The result is the same data type as b. Decimal values are truncated.
power(x,y)	all numeric types	float	x to the power of y (identical to x ** y)
sin(n)	all numeric types	float	Sine of n; returns a value from -1 to 1.
sqrt(n)	all numeric types and money	float	Square root of n.

For trigonometric functions (atan(), cos(), and sin()), specify arguments in radians. To convert degrees to radians, use the following formula:

radians = degrees/360 * 2 * pi

To obtain a tangent, divide sin() by cos().

String Functions

String functions perform a variety of operations on character data. String functions can be nested. For example:

left(right(x.name, size(x.name) - 1), 3)

returns the substring of x.name from character positions 2 through 4, and

concat(concat(x.lastname, ', '), x.firstname)

concatenates x.lastname with a comma and then concatenates x.firstname with the first concatenation result. The + operator can also be used to concatenate strings:

x.lastname + ', ' + x.firstname

The following string functions do not accept long varchar or long byte columns:

• Locate

• Pad

• Shift

• Squeeze

• Trim

• Notrim

• Charextract

To apply any of the preceding functions to a long varchar or long byte column, first coerce the column to an acceptable data type. For example:

squeeze(varchar(long_varchar_column))

If a coercion function is applied to a long varchar or long byte value that is longer than 1928 characters or bytes, the result is truncated to 1928 characters or bytes.

The following table lists the string functions supported in OpenSQL. The expressions c1 and c2, representing the arguments, can be any of the string types, except where noted. The expressions len and nshift represent integer arguments.

Name	Result Type	Description
charextract(c1,n)	varchar	Returns the nth byte of c1. If n is larger than the length of the string, then the result is a blank character.
concat(c1,c2)	any character data type, byte	Concatenates one string to another. The result size is the sum of the sizes of the two arguments. If the result is a c or char string, it is padded with blanks to achieve the proper length. To determine the data type results of concatenating strings, see the table regarding results of string concatenation.
left(c1,len)	any character data type	Returns the leftmost len characters of c1. If the result is a fixed-length c or char string, it is the same length as c1, padded with blanks. The result format is the same as c1.
length(c1)	smallint (for long varchar, returns 4-byte integer)	If c1 is a fixed-length c or char string, returns the length of c1 without trailing blanks. If c1 is a variable-length string, returns the number of characters actually in c1.
locate(c1,c2)	smallint	Returns the location of the first occurrence of c2 in c1, including trailing blanks from c2. The location is in the range 1 to size(c1). If c2 is not found, the function returns size(c1) + 1. The function size() is described below, in this table. If c1 and c2 are different string data types, c2 is coerced into the c1 data type.
lowercase(c1) or lower(c1)	any character or Unicode data type	Converts all upper case characters in c1 to lower case.
pad(c1)	text or varchar	Returns c1 with trailing blanks appended to c1; for instance, if c1 is a varchar string that could hold fifty characters but only has two characters, then pad(c1) appends 48 trailing blanks to c1 to form the result.
right(c1,len)	any character data type	Returns the rightmost len characters of c1. Trailing blanks are not removed first. If c1 is a fixed-length character string, the result is padded to the same length as c1. If c1 is a variable-length character string, no padding occurs. The result format is the same as c1.
shift(c1,nshift)	any character data type	Shifts the string nshift places to the right if nshift > 0 and to the left if nshift < 0. If c1 is a fixed-length character string, the result is padded with blanks to the length of c1. If c1 is a variable-length character string, no padding occurs. The result format is the same as c1.
size(c1)	smallint	Returns the declared size of c1 without removal of trailing blanks.
soundex(c1)	any character data type	Returns a c1 four-character field that can be used to find similar sounding strings. For example, SMITH and SMYTHE produce the same soundex code. If there are less than three characters, the result is padded by trailing zero(s). If there are more than three characters, the result is achieved by dropping the rightmost digit(s). This function is useful for finding like-sounding strings quickly. A list of similar sounding strings can be shown in a search list rather than just the next strings in the index.
squeeze(c1)	text or varchar	Compresses white space. White space is defined as any sequence of blanks, null characters, newlines (line feeds), carriage returns, horizontal tabs and form feeds (vertical tabs). Trims white space from the beginning and end of the string, and replaces all other white space with single blanks. This function is useful for comparisons. The value for c1 must be a string of variable‑length character string data type (not fixed-length character data type). The result is the same length as the argument.
substring(c1 from n1 [for n2])	varchar or nvarchar	Returns a substring of parameter c1 starting at offset n1. If n2 is specified, the resulting string is min(n2, length(c1)-n1) in length. If n1 is 0 or negative, the resulting substring starts with the 1st byte of c1. If n1 > length(c1), the resulting string has length 0. If n2 is negative, an error is returned.
trim(c1)	text or varchar	Returns c1 without trailing blanks. The result has the same length as c1.
notrim(c1)	any character string variable	Retains trailing blanks when placing a value in a varchar column. This function can only be used in an embedded OpenSQL program. For more information, see the Embedded SQL Companion Guide.
uppercase(c1) or upper(c1)	any character or Unicode data type	Converts all lower case characters in c1 to upper case.

String Concatenation Results

The following table shows the results of concatenating expressions of various character data types:

1st String	2nd String	Trim Blanks		Result Type
		from 1st?	from 2nd?
c	c	Yes	--	c
c	text	Yes	--	c
c	char	Yes	--	c
c	varchar	Yes	--	c
c	long varchar	Yes	No	long varchar
text	c	No	--	c
char	c	Yes	--	c
varchar	c	No	--	c
long varchar	c	No	No	long varchar
text	text	No	No	text
text	char	No	Yes	text
text	varchar	No	No	text
text	long varchar	No	No	long varchar
char	text	Yes	No	text
varchar	text	No	No	text
long varchar	text	No	No	long varchar
char	char	No	--	char
char	varchar	No	--	char
char	long varchar	No	No	long varchar
varchar	char	No	--	char
long varchar	char	No	No	long varchar
varchar	varchar	No	No	varchar
long varchar	long varchar	No	No	long varchar

When concatenating more than two operands, expressions are evaluated from left to right. For example:

varchar + char + varchar

is evaluated as:

(varchar+char)+varchar

To control concatenation results for strings with trailing blanks, use the trim, notrim, and pad functions.

Date Functions

OpenSQL supports functions that derive values from absolute dates and from interval dates. These functions operate on columns that contain date values. An additional function, dow(), returns the day of the week (mon, tue, and so on) for a specified date. For a description of the dow() function, see Data Type Conversion Functions.

Some date functions require you to specify a unit parameter; unit parameters must be specified using a quoted string. The following table lists valid unit parameters:

Date Portion	How Specified
Second	second, seconds, sec, secs
Minute	minute, minutes, min, mins
Hour	hour, hours, hr, hrs
Day	day, days
Week	week, weeks, wk, wks
ISO-Week	iso-week, iso-wk
Month	month, months, mo, mos
Quarter	quarter, quarters, qtr, qtrs
Year	year, years, yr, yrs

The date functions are as follows:

DATE_TRUNC

date_trunc(unit,date)

Result Type: date

Returns a date value truncated to the specified unit.

DATE_PART

date_part(unit,date)

Result Type:integer

Returns an integer containing the specified (unit) component of the input date.

DATE_GMT

date_gmt(date)

Result Type: Any character data type

Converts an absolute date into the Greenwich Mean Time character equivalent with the format yyyy_mm_dd hh:mm:ss GMT. If the absolute date does not include a time, the time portion of the result is returned as 00:00:00.

select date_gmt('1-1-98 10:13 PM PST')

returns 1998_01_01 06:13:00 GMT, while

select date_gmt('1-1-1998')

returns 1998_01_01 00:00:00 GMT

GMT_TIMESTAMP

gmt_timestamp(s)

Result Type: Any character data type

Returns a twenty-three-character string giving the date s seconds after January 1, 1970 GMT. The output format is 'yyyy_mm_dd hh:mm:ss GMT'.

select (gmt_timestamp (1234567890))

returns the following value 2009_02_13 23:31:30 GMT.

With II_TIMEZONE_NAME = AUSTRALIA_BRISBANE, the query

select date(gmt_timestamp (1234567890))

returns 14-feb-2009 09:31:30

INTERVAL

interval (unit,date_interval)

Result Type: float

Converts a date interval into a floating-point constant expressed in the unit of measurement specified by unit. The interval function assumes that there are 30.436875 days per month and 365.2425 days per year when using the mos, qtrs, and yrs specifications.

select(interval('days', '5 years'))

returns 1826.213

This function is not supported for the Oracle and MS SQL Enterprise Access products.

_DATE

_date(s)

Result Type:Any character data type

Returns a nine-character string giving the date s seconds after January 1, 1970 GMT. The output format is dd-mmm-yy.

select _date(123456)

returns 2-jan-70

_DATE4

_date4(s)

Result Type: Any character data type

Returns an eleven-character string giving the date s seconds after January 1, 1970 GMT. The output format is controlled by the II_DATE_FORMAT setting.

With II_DATE_FORMAT set to US, the query:

select _date4(123456)

returns 02-jan-1970

while with II_DATE_FORMAT set to MULTINATIONAL, the query:

select _date4(123456)

returns 02/01/1970

_TIME

_time(s)

Result Type: Any character data type

Returns a five-character string giving the time s seconds after January 1, 1970 GMT. The output format is hh:mm (seconds are truncated).

select _time(123456)

returns 02:17

Date_trunc Function

Use the date_trunc function to group all the dates in the same month or year, and so forth. For example:

date_trunc('month',date('23-oct-1998 12:33'))

returns 1-oct-1998, and

date_trunc('year',date('23-oct-1998'))

returns 1-jan-1998.

Truncation takes place in terms of calendar years and quarters (1-jan, 1-apr, 1-jun, and 1-oct).

To truncate in terms of a fiscal year, offset the calendar date by the number of months between the beginning of your fiscal year and the beginning of the next calendar year (6 mos for a fiscal year beginning July 1, or 4 mos for a fiscal year beginning September 1):

date_trunc('year',date+'4 mos') - '4 mos'

Weeks start on Monday. The beginning of a week for an early January date may fall into the previous year.

Date_part Function

This function is useful in set functions and in assuring correct ordering in complex date manipulation. For example, if date_field contains the value 23-oct-1998, then:

date_part('month',date(date_field))

returns a value of 10 (representing October), and

date_part('day',date(date_field))

returns a value of 23.

Months are numbered 1 to 12, starting with January.

Hours are returned according to the 24-hour clock.

Quarters are numbered 1 through 4.

Week 1 begins on the first Monday of the year. Dates before the first Monday of the year are considered to be in week 0. However, if you specify ISO-Week, which is ISO 8601 compliant, the week begins on Monday, but the first week is the week that has the first Thursday. The weeks are numbered 1 through 53.

Therefore, if you are using Week and the date falls before the first Monday in the current year, date_part returns 0. If you are using ISO-Week and the date falls before the week containing the first Thursday of the year, that date is considered part of the last week of the previous year, and date_part returns either 52 or 53.

The following table illustrates the difference between Week and ISO-Week:

Date Column	Day of Week	Week	ISO-Week
02-jan-1998	Fri	0	1
04-jan-1998	Sun	0	1
02-jan-1999	Sat	0	53
04-jan-1999	Mon	1	1
02-jan-2000	Sun	0	52
04-jan-2000	Tue	1	1
02-jan-2001	Tue	1	1
04-jan-2001	Thu	1	1

Bitwise Functions

Bitwise functions operate from right to left, with shorter operands padded with binary zeroes to the left. Each result is a byte field the size of the longer operand, except BIT_NOT, which takes a single byte operand and returns the same-sized operand.

The external bitwise functions are:

BIT_ADD

Returns the logical "add" of two byte operands; any overflow is disregarded.

"Logical add" is adding the binary content of the two operands as if they were unsigned binary integers.

If the sum does not fit in the result field, the overflow is disregarded and the rest is retained. For example, the result of adding 200 and 90 would be 290, but 290 does not fit in one byte (maximum value is 255), so the overflow is disregarded and the logical add result is 34 (290 minus 256).

BIT_AND

Returns the logical "and" of two byte operands. If two bits are 1, the answer is 1; otherwise the answer is 0.

BIT_NOT

Returns the logical "not" of a single byte operand.

BIT_OR

Returns the logical "or" of two byte operands. If either or both bits are 1, the answer is 1.

BIT_XOR

Returns the logical "xor" of two byte operands. If either, but not both, bits is 1, the answer is 1; otherwise the answer is 0.

INTEXTRACT(byte, n)

Similar to the CHAREXTRACT string function. Returns the nth byte as an integer. For example: intextract(X'0A020C04', 3) returns 12, which is the integer value for the third byte '0C'.

n is an integer field. If n is less than 1 or larger than the number of bytes available, then a 0 is returned. For example: intextract(X'0A020C04', -2) or intextract(X'0A020C04', 5) both return 0.

Hash Function

This function is used to generate a four-byte numeric value from expressions of all data types except long data types. Note that the implicit size for the expression can affect the result. For example:

select hash(1), hash(int1(1)), hash(int2(1)), hash(int4(1))\g

returns the following single row:

Col1	Col2	Col3	Col4
-920527466	1526341860	-920527466	-1447292811

Note: Since the constant 1 is implicitly a short integer, only the return values for Hash(1) and Hash(int2(1)) match. For the remaining columns, the difference in the number of bytes holding the integer leads to a different hash value. Also note that the generated hash value is not guaranteed unique, even if the input values are unique. The hash function is not supported for Enterprise Access products.

Random Number Functions

The random number function is used to generate random values. Use the following statement to set the beginning value for the random functions:

[exec sql] set random_seed [value]

There is a global seed value and local seed values. The global value is used until you issue “set random_seed,” which changes the value of the local seed. Once changed, the local seed is used for the whole session. If you are using the global seed value, the seed is changed whenever a random function executes. This means that other users issuing random calls will enhance the “randomness” of the returned value. Note that the seed value can be any integer.

If you omit the value, then Ingres multiplies the process ID by the number of seconds past 1/1/2016 until now. This value generates a random starting point. You can use value to run a regression test from a static start and get identical results.

There are four random number functions:

• random() - Returns a random integer based on a seed value.

• randomf() - Returns a random float based on a seed value between 0 and 1. This is slower than random, but produces a more random number.

• random(l,h) - Returns a random integer in the specified range
(that is, l >= x <= h).

• randomf(l,h) - Passing two integer values generates an integer result in the specified range; passing two floats generates a float in the specified range; passing an int and a float causes them to be coerced to an int and generates an integer result in the specified range (that is, l >= x <= h).

Last modified date: 04/03/2024