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).
|
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. |
|
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:
- To add a leading or trailing zero to the result, add a zero (0) to the format string. For example:
Field value
Mask
Return value
1.49
'$000,000.00'
'$000,001.49'
- To replace a leading zero with a blank space, put an uppercase ‘B’ in the position of the zero. A ‘B’ in any other position has no effect. For example:
Field value
Mask
Return value
0035
'BBB.BBB'
' 35.000'
- To specify a placeholder for a digit in the field value, use any non-0 number. If there is no corresponding digit in the field, the numeral will be replaced by a space in the result.
Field value
Mask
Return value
1.49
'$999,999.99'
' $1.49'
- To include symbols, punctuation marks, or letters in the result, add them to the format string. Only the dollar sign and period (decimal point) are permitted in the first (left-most) position. Any other character in that position will be replaced by a blank, as illustrated in the first example below. If a result contains a period with no digits preceding it, a zero will be placed there, as shown in the last two examples. If a letter is not preceded by any digits, it will be replaced by a blank space. You cannot use uppercase B in the format since it has a special use.
Field value
Mask
Return value
65000
'*99999'
' 65000'
65000
'99999*'
'65000*'
65000
'$99999'
'$65000'
.65000
'.99999'
'.65000'
.65000
'000.00%'
'000.65%'
4500
'9999 Euro'
'4500 Euro'
.35
'$99999.99'
' $0.35'
.35
'99999.99'
' 0.35'
If col is a date/time column, you can use the following format 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 |
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:
- TO_DATE supports all the masks listed except MONTH, Month, month, D, DAY, Day, day, DY, Dy, dy, Q, W, and WW.
- The AM, PM, am, and pm masks work only if the TO_DATE clause is cast as SQL_TIMESTAMP or SQL_TIME. For example, the following returns “1990-06-10 22:10:02.000000”. (The returned hour would be 10 if the value had no AM/PM indicator and the mask didn’t include an AM, PM, am, or pm mask.)
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 <. Every right angle bracket (>) will be replaced with >.
TRANSLATE(desc,'<>','<>')
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.
