Scalar

There are four types of scalar functions:

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