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.

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

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

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.

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

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

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