Scalar functions

Scalar functions return a single value for each returned row. The following scalar functions are supported by xfODBC. Parameters with “exp” represent expressions or the results of expressions (e.g., str_exp represents a string expression or the string produced by an expression).

Note

Most scalar functions are not designed to evaluate null values, so it is best to include an AND IS NOT NULL clause when using a scalar function.

Important

Avoid converting a column to an expression if the column resolves to an index used for optimization. This will likely prevent optimization. see Setting formats for dates and times for more information.

For links to more information on xfODBC's support for SQL, see Appendix B: SQL Support. For information on using scalar functions with date and time data, see Setting runtime data access options.

ABS

ABS(num_exp)

This function returns the absolute value of a numeric expression. For example, if num_exp produces the value -5, the ABS function will return 5.

ASCII

ASCII(str_exp)

This function returns the ASCII code (an integer value) for the leftmost character in str_exp. For example, if str_exp produces the string “Main Street Plants”, ASCII will return 77, the ASCII decimal code for M.

CAST

CAST(exp|NULL AS datatype)

This function converts an expression (exp) or null to the specified data type. Datatype is one of the following:

SQL_BIGINT

SQL_DOUBLE

SQL_TIME

SQL_BIT

SQL_FLOAT

SQL_TIMESTAMP

SQL_CHAR

SQL_INTEGER

SQL_TINYINT

SQL_DATE

SQL_NUMERIC

SQL_VARCHAR

SQL_DECIMAL

SQL_SMALLINT

 

CAST also supports the database data types listed in CREATE TABLE—for example, integer and number(n). Note, however, that no truncation occurs and that n in the following is ignored: numeric(n), char(n), and varchar(n).

If the specified data type cannot store the entire result, the data will either be truncated or you will get a “data truncated” warning, and the field that caused the warning will have an undetermined value. For alphanumeric data types, the data will be truncated. For numeric data types, the data will be truncated only if truncating removes the fractional portion of the numeric data.

The following example returns term codes as SQL_NUMERIC values.

SELECT CAST(or_terms AS SQL_NUMERIC) 
  FROM orders WHERE or_odate < '1995-01-01'

The next example returns null values as integer values. (By default, null values are returned as char values.) Without the CAST statement, the CASE statement wouldn’t work because all of its return values, including the return value for ELSE, must have the same data type.

SELECT CASE  in_shape 
  WHEN 'tree' THEN 1 
  ELSE CAST(NULL AS SQL_INTEGER) 
  END 
FROM plants

CHAR_LENGTH or CHARACTER_LENGTH

CHAR_LENGTH(exp)

or

CHARACTER_LENGTH(exp)

This function returns the number of characters or bytes in a string. If the expression (exp) results in a character data type, this function returns the number of characters in the resulting string. Otherwise, it returns the number of bytes in the resulting string. This number is the smallest integer that’s greater than or equal to the number of bits divided by 8.

CHR

CHR(numeric_exp)

This function returns the ASCII character equivalent of numeric_exp.

CONCAT

CONCAT(str_exp1, str_exp2)

This function returns a string that’s the concatenation of str_exp1 and str_exp2.

CONVERT

CONVERT(exp, datatype)

This function converts an expression (exp) to a specified data type (datatype). Datatype is one of the keywords listed for the CAST scalar function (see CAST above). Data may be truncated if the specified data type cannot store the entire result of the expression. For example, if you convert a bigint to a decimal, you may lose precision.

You may need to use the ODBC escape sequence (“{fn” and an ending brace “}”) with CONVERT:

SELECT or_number, {fn CONVERT(or_number, SQL_BIGINT)}, 
                  {fn CONVERT(or_number, SQL_SMALLINT)} 
  FROM orders

CURDATE

CURDATE()

This function returns the current date for the database server. It is not altered for time zones of client systems.

CURDATETIME

CURDATETIME()

This function returns the current date and time for the database server. It is not altered for time zones of client systems. The hour is returned as an integer in the range of 0-23.

CURRENT_DATE

CURRENT_DATE()

This function returns the current date for the database server. It is not altered for time zones of client systems.

CURRENT_DATETIME

CURRENT_DATETIME()

This function returns the current date and time for the database server. It is not altered for time zones of client systems. The hour is returned as an integer in the range of 0-23.

CURRENT_TIME

CURRENT_TIME()

This function returns the current time for the database server. It is not altered for time zones of client systems. The hour is returned as an integer in the range of 0-23.

CURRENT_TIMESTAMP

CURRENT_TIMESTAMP()

This function returns the timestamp, including microseconds, for the current time for the database server. It is not altered for time zones of client systems. The hour is returned as an integer in the range of 0-23.

CURTIME

CURTIME()

This function returns the current time for the database server. It is not altered for time zones of client systems. The hour is returned as an integer in the range of 0-23.

CURTIMESTAMP

CURTIMESTAMP()

This function returns the timestamp, including microseconds, for the current time for the database server. It is not altered for time zones of client systems. The hour is returned as an integer in the range of 0-23.

DATABASE

DATABASE()

This function returns the name of the connected database.

DAYNAME

DAYNAME(date_exp)

This function returns the name of the day of the week for the date specified by date_exp.

For example, if the value of or_edate in the sample database is 2000-10-01, the following will return “Sunday”.

SELECT DAYNAME(or_edate) FROM orders WHERE or_vendor=44

DECODE

DECODE(col, exp1, return1[, exp2, return2]...[, default]) 

This function returns different values depending on the value of an expression (col). If col matches an exp* value (exp1, exp2, etc.), DECODE returns the corresponding return* value. If col doesn’t match an exp* value, DECODE returns the default value or NULL if default isn’t passed.

All return expressions must be the same data type.

The following example returns ‘RED ZONE’ if the value for in_zone is 2. If the value for in_zone is 4, the statement returns ‘BLUE ZONE’. If in_zone is neither 2 nor 4, the statement returns ‘BLACK ZONE’, the default.

SELECT DECODE(in_zone, 2, 'RED ZONE', 4, 'BLUE ZONE', 'BLACK ZONE')
    FROM plants

GREATEST

GREATEST(exp, [exp2, ...])

This function returns the greatest of a specified set of values (exp, exp2, etc.).

For example, when used with the sample database distributed with Connectivity Series, the following query returns 2000-10-01, which is the value for or_odate. (For order number 7, or_odate is 2000-10-1 and or_edate is 1993-03-07.)

SELECT GREATEST(or_odate, or_edate) FROM orders 
    WHERE or_number=7

HOUR

HOUR(time_exp)

This function returns the hour portion of the return value for a time expression (time_exp). The hour is returned as an integer in the range of 0-23.

IFNULL

IFNULL(exp, return_if_null)

Depending on whether a specified expression is null, this function returns either the expression itself or a different value. If exp is null, IFNULL returns return_if_null. If exp is not null, exp is returned.

Return_if_null must have a data type that’s compatible with the data type of exp. For example, the following will not work:

SELECT IFNULL(or_sdate, '1999-04-05') FROM orders

The next example, however, is correct:

SELECT IFNULL(or_sdate, {d '1999-04-05'}) FROM orders

xfODBC interprets zero-length strings as nulls, so clauses like the following are unnecessary: IFNULL(strng, ‘’).

INSTR

INSTR(str_exp1, str_exp2[, n[, m]])

This function returns the position of the first character of a string (str_exp2) within another string (str_exp1).

If the n argument is not specified, INSTR searches str_exp1 and returns the position of the first character in the first occurrence of str_exp2. If the n argument is specified, INSTR begins the search n characters into str_exp1. If the n argument is negative, the search begins n characters from the end of str_exp1. If m is specified, the position of the mth occurrence of str_exp2 in str_exp1 is returned. If str_exp2 does not exist in str_exp1, this function returns 0.

LCASE

LCASE(str_exp)

This function converts any uppercase characters in a string (str_exp) to lowercase characters, and returns the resulting string.

LEAST

LEAST(exp, [exp2, ...])

This function returns the least of the specified values (exp, exp2, etc.). For example, when used with the sample database distributed with Connectivity Series, the following query returns 1993-03-07, which is the value for or_edate. (For order number 7, or_odate is 2000-10-1 and or_edate is 1993-03-07.)

SELECT LEAST(or_odate, or_edate) FROM orders WHERE or_number=7

LEFT

LEFT(str_exp, n)

This function returns the first n characters of a string (str_exp). For example, if str_exp produces the string “ablebaker” and the value of n is 4, LEFT returns “able”.

You must use the ODBC escape sequence ("{fn" and an ending brace "}") with LEFT:

SELECT {fn LEFT(cust_name, 4)} FROM customers

LENGTH

LENGTH(str_exp)

This function returns the number of characters in a string (str_exp) minus any trailing blanks.

LOCATE

LOCATE(str_exp1, str_exp2[, n[, m]])

This function returns the position of the first character of a string (str_exp1) in a string (str_exp2).

If the n argument is not specified, LOCATE searches str_exp2 and returns the position of the first character in the first occurrence of str_exp1. If the n argument is specified, LOCATE begins the search n characters into str_exp2. If the n argument is negative, the search begins n characters from the end of str_exp2. If m is specified, the position of the mth occurrence of str_exp1 in str_exp2 is returned. If str_exp1 does not exist in str_exp2, this function returns 0.

LTRIM

LTRIM(str_exp)

This function returns a string (str_exp) with leading blanks removed.

NOW

NOW()

This function returns the current date and time for the database server. It is not altered for time zones of client systems. The hour is returned as an integer in the range of 0-23.

NVL

NVL(exp1, exp2)

For a given expression (exp1), this function either returns the same expression (if it’s not null) or a different expression (exp2) if it is null. (Exp2 must have the same data type as exp1.)

This function enables you to create queries like the following that substitute a string or value for null.

SELECT in_name, NVL(in_color, 'N/A')
    FROM plants

POSITION

POSITION(char_exp1 IN char_exp2)

This function returns the position of a character expression (char_exp1) in another character expression (char_exp2). If char_exp1 does not exist in char_exp2, This function returns 0.

REPLACE

REPLACE(str_exp1, str_exp2, str_exp3)

This function searches for a string (str_exp2) in a string (str_exp1) and replaces occurrences of the found string with another string (str_exp3). For example, the following returns “baker st.”:

REPLACE('baker street', 'street', 'st.')

REVERSE

REVERSE(string_exp)

This function reverses the order of the characters returned for a string expression. For example, the following returns “cba”:

REVERSE('abc')

RIGHT

RIGHT(str_exp, n) 

This function returns a given number of characters (n) from the end of a string (str_exp). For example, if the value of str_exp is “Border Imports”, and n is set to 7, RIGHT returns “Imports”.

You must use the ODBC escape sequence (“{fn” and an ending brace “}”) with RIGHT:

SELECT {fn RIGHT(cust_name, 4)} FROM customers

ROUND

ROUND(num_exp[, int_exp])

This function rounds a numeric expression (num_exp). If int_exp is not specified, ROUND returns num_exp rounded to a whole number. If int_exp is passed and is positive, ROUND rounds num_exp to int_exp places to the right of the decimal point. If int_exp is negative, num_exp is rounded to int_exp places left of the decimal point.

RTRIM

RTRIM(str_exp)

This function removes trailing blanks from a string (str_exp), and returns the resulting string.

SQRT

SQRT(numeric_exp)

This function returns the square root of a numeric expression (numeric_exp). It supports only non-negative real numbers. A negative number will cause an error during SQLFetch. To avoid such errors, we suggest you add a WHERE clause that eliminates rows that have negative values for the column passed to SQRT.

SUBSTR or SUBSTRING

SUBSTR(str_exp, n[, m])

or

SUBSTRING(str_exp, n[, m])

This function returns the substring of a string expression (str_exp) that begins at a given position (n) and has a given length (m characters long).

If n equals 0, the entire string is returned. If n is a negative number, this function begins n characters (or spaces) from the end of the string and returns m characters. For example, if the database has “Main Street Plants” as a customer name, the following statement returns “Street” for that customer.

SELECT SUBSTR(cust_name, -13, 6) FROM customers

If you don’t specify m, SUBSTR returns all characters from the character in the n position to the end of the string.

SYSDATE

SYSDATE

This function returns the current system date and time for the client system. The hour is returned as an integer in the range of 0-23.

TO_CHAR

TO_CHAR(col[, format])

This function converts date, numeric, and date/time (timestamp) column data into a character string.

If col is a date column and no format argument is passed, the default, YYYY-MM-DD, is used.

If col is a numeric column, you can format it in the following ways:

If col is a date/time column, you can use the following format masks:

Date Masks

Mask

Description

AM, PM, am, pm

Two-digit meridian indicator (AM, PM, am, or pm). Case of first character determines case of indicator.

Da

Single-digit day of the week (1-7). Weeks start on Sunday.

DAYa

Full name of day in uppercase characters

Daya

Full name of day with initial character capitalized

daya

Full name of day in lowercase characters

DD, dd

Two-digit day of the month (01-31)

DDD

Three-digit day of the year (001-356)

DYa

Uppercase three character day

Dya

Three character day with initial character capitalized

dya

Lowercase three character day

HH, HH24

Two-digit hour (00-23)

HH12

Two-digit hour (00-11)

J

Julian day (does not support BC dates)

MI

Two-digit minute (00-59)

MM, mm

Two-digit month (01-12)

MON

Uppercase three character month

Mon

Three character month with initial character capitalized

mon

Lowercase three character month

MONTHa

Full name of the month in uppercase characters

Montha

Full name of the month with initial character capitalized

montha

Full name of the month in lowercase characters

Qa

Single-digit quarter (1-4)

RR

Two-digit year from another century (a sliding window format based on 20)

SS

Two-digit second (00-59)

SSSSS

Number of seconds past midnight (00000-86399)

UUUUUU

Microsecond

Wa

Single-digit week of month (1-4)

WWa

Two-digit week of year (01-52)

YY

Two-digit year

YYYY

Four-digit year

a. Not supported by TO_DATE.

To add the letters (th, rd, and so forth) needed to create ordinal numbers, such as “5th” or “3rd”, add “th” to any uppercase digit mask. For example, if or_odate is 1993-03-01, the following will return 2ND because 1993-03-01 was a Monday.

SELECT TO_CHAR(or_odate, 'Dth') FROM orders WHERE or_number = 3

Given the same date, the next example returns “060th day of 1993”. If you want the resulting string to include mask characters, enclose the character(s) in quotation marks, as we did with “day”.

SELECT to_char(or_odate, 'dddth "day" of YYYY') FROM orders 
    WHERE or_number = 3

The following example retrieves or_odate values and formats them in month, day, four-digit year order (MMDDYYY):

SELECT to_char (or_odate, 'MMDDYYYY') FROM orders

TO_DATE

TO_DATE(str_exp[, format])

This function converts a string expression (str_exp) to a date or date/time (timestamp) data type. If no format is specified, the default date format is used (YYYY-MM-DD). For example:

SELECT TO_DATE(or_odate) FROM orders

For information on formats, see the Date Masks table, but note these restrictions:

SELECT CAST(TO_DATE('10-06-1990 10:10:02 PM',
    'DD-MM-YYYY HH:MI:SS AM') AS SQL_TIMESTAMP)
    FROM dual

TO_NUMBER

TO_NUMBER(exp)

This function converts the results of a string or character expression (exp) into a numeric value. The exp argument can be a character or a string, but it must contain data. For example:

SELECT or_number, {fn TO_NUMBER(or_item)} FROM orders 

TRANSLATE

TRANSLATE(str_exp, str_exp_from, str_exp_to)

This function replaces characters in a string. It returns str_exp after replacing each character in str_exp_from with the character(s) in the corresponding position in str_exp_to. For example, the following TRANSLATE clause results in the string “1bcd23”:

TRANSLATE('AbcdEF', 'AEF', '123')

If str_exp_to is empty, all characters in str_exp_from are removed. The following example results in the string “def”:

TRANSLATE('abcdef', 'abc', '')

Str_exp_from cannot be larger than str_exp_to. (If it is, you will get the error “Illegal parameters for function TRANSLATE”.) Str_exp_from, however, can be smaller than str_exp_to, as long as the number of characters in str_exp_from is a multiple of the number of characters in str_exp_to.

For example, the following will work (if desc is large enough to hold the result). Every left angle bracket (<) will be replaced with &lt;. Every right angle bracket (>) will be replaced with &gt;.

TRANSLATE(desc,'<>','&lt;&gt;')

If you want to change the entire value for a column (rather than just selected characters), use DECODE.

TRUNC

TRUNC(num_exp | datetime_exp)

This function removes the fractional portion of a number (num_exp) or returns a date/time value (datetime_exp) with the time portion set to zeros.

For example, if the value for a numeric field in a given record is 1.05, applying the TRUNC function to the field will return the value 1, as in the following:

SELECT TRUNC(or_price) FROM orders WHERE or_number=3

The next series of SQL statements results in “1993-03-01 00:00:00”:

CREATE TABLE date_table(datetime_field datetime)
INSERT INTO date_table (datetime_field) 
  VALUES ('1993-03-01 17:02:20')

SELECT TRUNC(datetime_field) FROM date_table

UCASE

UCASE(str_exp)

This function converts lowercase characters in a string expression (str_exp) to uppercase characters and returns the resulting string.

USER

USER()

This function returns the name of the user for the data source, which may be different than the name used to log in to the data source.