2. Language Elements : System-defined Functions : SQL Functions : Scalar Functions
 
Share this page                  
Scalar Functions
The scalar functions require either one or two single-value arguments. Scalar functions can be nested to any level.
The types of scalar functions are as follows:
• Data type conversion
• Numeric
• String
• Date
• Hash
• Random number
Note:  If II_DECIMAL is set to comma, be sure that when SQL syntax requires a comma (such as a list of table columns or SQL 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
OpenROAD provides the following data type conversion functions:
Name
Operand
Type
Result Type
Description
c(expr)
any
c
Converts any value to a c string
char(expr)
any
char
Converts any value to a char string
date(expr)
c, char,
text, varchar
date
Converts a c, char, varchar, or text string to internal date representation
decimal(expr,
1 < p <31
0 < s <p)
c, char, varchar, text, float, money, integer(1), smallint, integer
decimal
Returns the decimal representation of the argument string
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, integer(1), smallint, integer
float4
Converts the specified expression to float4
float8(expr)
c, char, varchar, text, float, money, integer(1), smallint, integer
float
Converts the specified expression to float
hex(expr)
varchar,
c, char,
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, integer(1), smallint, integer
integer1
Converts the specified expression to integer1; floating point values are truncated
int2(expr)
c, char, varchar, text, float, money, integer(1), smallint, integer
smallint
Converts the specified expression to smallint; floating point values are truncated
int4(expr)
c, char, varchar, text, float, money, integer(1), smallint, integer
integer
Converts the specified expression to integer; floating point values are truncated
money(expr)
c, char, varchar, text, float, integer(1),
smallint,
integer,
money
Converts the specified expression to internal money representation; rounds floating point values, if necessary
text(expr)
any
text
Converts any value to a text string; this function removes any trailing blanks from c or char string expressions
varchar(expr)
any
varchar
Converts any value to a varchar string; this function removes any trailing blanks from c or char string expressions
Numeric Functions
SQL supports the numeric functions listed in the following table:
Numeric Function Name
Operand Type
Result Type
Description
ABS(n)
all numeric types and money
same as n
Absolute value of n.
ACOS(n)
all numeric types
float
Arccosine of cosine value n
ASIN(n)
all numeric types
float
Arcsine value of sine value n
ATAN(n)
all numeric types
float
Arctangent of n; returns a value from (-pi/2) to pi/2.
ATAN2 (x, y)
all numeric types
float
Arctangent of angle defined by coordinate pair (x, y)
CEIL(n)
CEILING(n)
all numeric types
decimal
Returns the smallest integer greater than or equal to the specified numeric expression.
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.
FLOOR(n)
all numeric types
decimal
Returns the largest integer less than, or equal to, the specified numeric expression.
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.
PI()
None
float
Value of pi (ratio of the circumference of a circle to its diameter)
ROUND(n,i)
all numeric types
decimal
Rounds value at the i'th place right or left of the decimal, depending on whether i is greater or less than 0.
SIGN(n)
all numeric types
integer
-1 if n < 0, 0 if n = 0, +1 if n > 0
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.
TAN(n)
all numeric types
float
Tangent value of angle n
For trigonometric functions (COS, SIN, TAN), specify argument in radians. To convert degrees to radians, use the following formula:
radians = degrees / 360 * 2 * pi()
The functions ACOS, ASIN, ATAN, AND ATAN2 return a value in radians.
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 concatenates x.firstname with the first concatenation result.
The + operator can also be used to concatenate strings:
x.lastname + ', ' + x.firstname
String Functions and the UTF-8 Character Set
Note:  For the UTF-8 character set, the character data is multibyte string and the actual number of bytes for the data could be more than the number of characters. If the output buffer for any string operation is not sufficiently large to hold the multibyte string, the result will be truncated at a character boundary.
String Functions Supported
The following table lists the string functions supported in SQL.
The expressions c1 and c2, representing function arguments, can be any of the string types (char, varchar, long varchar, c, text, byte, varbyte, long varbyte) or any of the Unicode types (nchar, nvarchar, long nvarchar), except where noted. The expressions len, n, n1, n2 or nshift, representing function arguments, are the integer type. For string functions operating on one of the string types, the integer arguments represent character (or 8-bit octet) counts or offsets. For string functions operating on one of the Unicode types, the integer arguments represent “code point” (or 16-bit Unicode characters) counts or offsets.
String Function Name
Result Type
Description
ASCII(v1)
any character type
Returns the character equivalent of the value v1, which is an expression of either character or numeric type.
CHAREXTRACT(c1,n)
varchar or nchar
Returns the nth character or code point of c1. If n is larger than the length of the string, the result is a blank character. It does not support long varchar or long nvarchar arguments.
CHARACTER_LENGTH(c1)
integer
Returns the number of characters in c1 without trimming blanks, as is done by the LENGTH() function.
This function does not support nchar and nvarchar arguments.
CHR(n)
character
Converts integer into corresponding ASCII code. If n is greater than 255, the conversion is performed on n mod 256.
CONCAT(c1,c2)
any character or Unicode 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.
This function does not support long nvarchar arguments.
LEFT(c1,len)
any character or Unicode 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.
This function does not support long nvarchar arguments.
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 within 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.
This function does not support long varchar or long nvarchar arguments.
LOWERCASE(c1) or LOWER(c1)
any character or Unicode data type
Converts all upper case characters in c1 to lower case.
LTRIM(expr)
any character data type
Returns character expression with leading blanks removed.
OCTET_LENGTH(c1)
integer
Returns the number of 8-bit octets (bytes) in c1 without trimming blanks, as is done by the LENGTH() function.
PAD(c1)
text, varchar, or nvarchar
Returns c1 with trailing blanks appended to c1. For example, if c1 is a varchar string that could hold 50 characters but only has two characters, then PAD(c1) appends 48 trailing blanks to c1 to form the result.
RIGHT(c1,len)
any character or Unicode 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.
This function does not support long nvarchar arguments.
RTRIM(expr)
any character data type
Returns character expression with trailing blanks removed.
SHIFT(c1,nshift)
any character or Unicode 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.
This function does not support long varchar or long nvarchar arguments.
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 digits.
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.
This function does not support long varchar or any Unicode arguments.
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.
This function does not support long varchar or long nvarchar arguments.
TRIM(c1)
text or varchar
Returns c1 without trailing blanks. The result has the same length as c1.
This function does not support long varchar or long nvarchar arguments.
UPPERCASE(c1) or UPPER(c1)
any character 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
text
c
No
--
C
char
c
Yes
--
C
varchar
c
No
--
C
text
text
No
No
text
text
char
No
Yes
text
text
varchar
No
No
text
char
text
Yes
No
text
varchar
text
No
No
text
char
char
No
--
char
char
varchar
No
--
char
varchar
char
No
--
char
varchar
varchar
No
No
varchar
nchar
nchar
No
No
nchar
nchar
nvarchar
No
No
nchar
nvarchar
nchar
No
No
nchar
nvarchar
nvarchar
No
No
nvarchar
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 and PAD functions.
Date Functions
OpenROAD supports functions that derive values from absolute dates and from interval dates. These functions operate on rows or variables that contain date values. An additional function, dow(), returns the day of the week (for example, mon, tue) for a specified date. The dow() function is described in Data Type Conversion Functions (see Data Type Conversion Functions).
The following date functions extract the specified portion of a date, time, or timestamp. They can be used with the ingresdate data types.
YEAR()
Extracts the year portion of a date.
QUARTER()
Extracts the quarter corresponding to the date. Quarters are numbered 1 through 4.
MONTH()
Extracts the month portion of a date.
WEEK()
Extracts the number of the week of the year that the date refers to. 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. Weeks are numbered 1 to 53.
WEEK_ISO()
Extracts the number of the week of the year that the date refers to, and conforms to ISO 8601 definition for number of the week. Week_iso begin on Monday, but the first week is the week that has the first Thursday of the year. 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.
DAY()
Extracts the day portion of a date.
HOUR()
Extracts the hour portion of a time.
MINUTE()
Extracts the minute portion of a time.
SECOND()
Extracts the second portion of a time.
MICROSECOND()
Extracts the fractions of seconds portion of a time as microseconds.
NANOSECOND()
Extracts the fractions of seconds portion of a time as nanoseconds.
Examples:
DAY('2006-12-15 12:30:55.1234') returns 15
SECOND('2006-12-15 12:30:55.1234') returns 55.1234
Date Functions for Date(ingresdate) Data Type
Note:  The functions described in this section apply only to the date(ingresdate) data type.
SQL 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 (see Data Type Conversion Functions).
Some date functions require specifying of 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, SES, 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 following table lists the date functions:
Date Function Name
Format (Result)
Description
DATE_TRUNC(unit,date)
date
Returns a date value truncated to the specified unit.
DATE_PART(unit,date)
integer
Returns an integer containing the specified (unit) component of the input date.
DATE_GMT(date)
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.
For example, the query:
SELECT DATE_GMT('1-1-98 10:13 PM PST')
returns the following value:
1998_01_01 06:13:00 GMT
while the query:
SELECT DATE_GMT(‘1-1-1998’)
returns:
1998_01_01 00:00:00 GMT
GMT_TIMESTAMP(s)
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’.
For example, the query:
SELECT (GMT_TIMESTAMP (1234567890))
returns the following value:
2009_02_13 23:31:30 GMT
while the query:
(II_TIMEZONE_NAME = AUSTRALIA-QUEENSLAND)
SELECT date(GMT_TIMESTAMP (1234567890))
returns:
14-feb-2009 09:31:30
INTERVAL (unit,date_interval)
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.
For example, the query:
SELECT(INTERVAL(‘days’, ‘5 years’))
returns the following value:
1826.213
ISDST(date)
integer
Returns 1 if date falls within Daylight Saving Time for the session timezone, else 0.
_DATE(s)
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.
For example, the query:
SELECT _DATE(123456)
returns the following value:
2-jan-70
Note that this function formats a leading space for day values less than 10.
_DATE4(s)
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.
For example, with II_DATE_FORMAT set to US, the query:
SELECT _DATE4(123456)
returns the following value:
02-jan-1970
while with II_DATE_FORMAT set to MULTINATIONAL, the query:
SELECT _DATE4(123456)
returns the following value:
02/01/1970
_TIME(s)
any character data type
Returns a five-character string giving the time s seconds after January 1, 1970 GMT, which is then adjusted for your local time zone. The output format is hh:mm (seconds are truncated).
For example, the query:
SELECT _TIME(123456)
returns the value 02:17 for the NA-PACIFIC time zone.
Truncate Dates using DATE_TRUNC Function
Use the DATE_TRUNC function to group all the dates within 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 falls into the previous year.
Using DATE_PART
The DATE_PART 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
HASH Functions
The HASH 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:  Because 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.
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]
The seed value can be any integer. 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 enhance the “randomness” of the returned value.
If you omit the value, Ingres multiplies the process ID by the number of seconds past 1/1/1970 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.
The random number functions are:
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 within the specified range (that is, l >= x <= h).
RANDOMF(l,h)
Passing two integer values generates an integer result within the specified range; passing two floats generates a float within the specified range; passing an int and a float causes them to be coerced to an int and generates an integer result within the specified range (that is, l >= x <= h).