Was this helpful?
Scalar
There are four types of scalar functions:
data type conversion (see Data Type Conversion Functions)
numeric (see Numeric)
string (see String)
date (see Date)
The scalar functions require either one or two single-value arguments. Scalar functions can be nested to any level.
Data Type Conversion Functions
The following table lists the data type conversion functions:
Name
Operand Type
Result Type
Description
c(expr [, len])
any
c
Converts argument to c string. If you specify the optional length argument, 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 you specify the optional length argument, 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.
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, and the integer data types
float4
Converts the specified expression to float4.
float8(expr)
c, char, varchar, text, float, money, and the integer data types
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, and the integer data types
i1
Converts the specified expression to i1. Floating point values are truncated. Numeric overflow occurs if the integer portion of a floating point value is too large to be returned in the requested format.
int2(expr)
c, char, varchar, text, float, money, and the integer data types
i2
Converts the specified expression to i2. Floating point values are truncated. Numeric overflow occurs if the integer portion of a floating point value is too large to be returned in the requested format.
int4(expr)
c, char, varchar, text, float, money, and the integer data types
i4
Converts the specified expression to i4. Floating point values are truncated. Numeric overflow occurs if the integer portion of a floating point value is too large to be returned in the requested format.
money(expr)
c, char,
varchar,
text,
float,
and the integer data types
money
Converts the specified expression to internal money representation. Rounds floating point values, if necessary.
text(expr [, len])
any
text
Converts argument to text string. If you specify the optional length argument, the function returns the leftmost len characters. Len must be a positive integer value that does not exceed the length of the expr string.
varchar(expr [, len])
any
varchar
Converts argument to varchar string. If you specify the optional length argument, 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 you omit the optional length parameter, the length of the result returned by data type conversion functions c(), char(), varchar(), and text() are as follows:
Data Type of Argument
Result Length
c
Length of operand
char
Length of operand
date
25 characters
float & float4
11 characters; 12 characters on IEEE computers
integer1 (smallint)
6 characters
integer
6 characters
integer4
13 characters
long varchar
Length of operand
money
20 characters
text
Length of operand
varchar
Length of operand
Numeric
The numeric functions are 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 and money
float
Arctangent of n; returns a value from
(-pi/2) to pi/2
cos(n)
all numeric types and money
float
Cosine of n; returns a value from -1 to 1
exp(n)
all numeric types and money
float
Exponential of n
log(n)
all numeric types and money
float
Natural logarithm of n
mod(n,b)
i4,
i2,
i1
same as b
n modulo b. The result is the same data type as b
sin(n)
all numeric types and money
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()), you must specify arguments in radians. To convert degrees to radians, use the following formula:
radians = degrees/360 * 2 * pi
To obtain a tangent, you must divide sin() by cos().
String
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. You can also use the + operator to concatenate strings:
x.lastname + ", " + x.firstname
The following table lists the string functions supported by QUEL. 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
byteextract(c1,n)
byte
Returns the nth byte of c1. If n is larger than the length of the string, the result is a byte of ASCII 0. It does not support long varchar or long nvarchar arguments.
concat(c1,c2)
Any character data type
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 following table, which shows the 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)
i2
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)
i2
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 c1's datatype.
lowercase(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 holds fifty characters but only has two characters, "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)
i2
Returns the declared size of c1 without removal of trailing blanks.
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.
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. You can only use this function in an embedded QUEL program. For more information, see the Embedded QUEL Companion Guide.
uppercase(c1)
any character or Unicode data type
Converts all lower case characters in c1 to upper case.
charextract(c1,n)
varchar
Returns the nth byte of c1. If n is larger than the length of the string, the result is a blank character.
soundex
any character data type
Returns a 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.
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
 
text
Yes
c
 
char
Yes
c
 
varchar
Yes
c
text
c
No
c
 
text
No
No
text
 
char
No
Yes
text
 
varchar
No
No
text
char
c
Yes
c
 
text
Yes
No
text
 
char
No
char
 
varchar
No
char
varchar
c
No
 
c
 
text
No
No
text
 
char
No
 
char
 
varchar
No
No
varchar
When concatenating more than two operands, the DBMS Server evaluates expressions 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
QUEL supports functions that derive values from absolute dates and from interval dates. These functions operate on columns that contain date values. 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:
Unit
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
Month
month, months, mo, mos
Quarter
quarter, quarters, qtr, qtrs
Year
year, years, yr, yrs
The following table lists the date functions:
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, blanks are returned for the time portion of the result.
For example, the query retrieve (dcolumn=date_gmt("1-1-93 10:13 PM PST")) returns the following value:
1998_01_01 06:13:00 GMT
gmt_timestamp(s)
Any character data type
Converts s (where s is an integer that represents the number of seconds since January 1, 1970 GMT) into the GMT character equivalent with the format yyyy_mm_dd hh:mm:ss GMT.
For example, the query
retrieve (dcolumn = gmt_timestamp(123456)) returns the following value:
1970_01_02 10:17:36 GMT
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 retrieve (icolumn = interval("days", "5 years")) returns the following value:
1826.213
_date(s)
Any character data type
Returns a 9-character string giving the date s seconds after January 1, 1970 GMT. The output format is "dd-mmm-yy".
For example, the query retrieve (dcolumn = _date(123456)) returns the following value:
2-jan-1970
_time(s)
Any character data type
Returns a 5-character string giving the time s seconds after January 1, 1970 GMT. The output format is "hh:mm" (seconds are truncated).
For example, the query retrieve (tcolumn = _time(123456)) returns the following value:
02:17
Last modified date: 08/28/2024