Built-in functions calculate values based on specified value
expressions. Built-in functions are sometimes called scalar
functions. See the Conditional_Expressions Help topic for
additional information.
1 – BITSTRING
The BITSTRING function extracts selected bits from a binary data
value. This functionality is primarily intended to query the bit
values stored in the RDB$FLAGS columns in the Rdb system table,
but can also be used for user data.
BITSTRING accepts numeric and date/time values and processes them
as bit arrays. The first (least significant) bit is numbered 1.
The most significant bit depends on the data type.
o TINYINT has 8 bits
o SMALLINT has 16 bits
o INTEGER has 32 bits
o BIGINT, DATE, TIME, TIMESTAMP and INTERVAL types have 64 bits
o The numeric expression after the FOR and FROM keywords must be
unscaled numeric values.
The following notes apply to usage of the BITSTRING function:
o If the numeric expression of the FOR clause is less than equal
to zero then it will be assumed equal to 1.
o If the FOR clause is omitted it will default to a value that
includes all remaining bits of the source value.
o If the FOR clause specifies a larger value than the number
of bits remaining in the source then will only return the
remaining bits.
Example: Using the BITSTRING function
Bit 1 in the RDB$FLAGS column of RDB$RELATIONS indicates that the
table is a view. This example uses this query to fetch the names
of all user defined views in the PERSONNEL database.
SQL> select rdb$relation_name
cont> from rdb$relations
cont> where rdb$system_flag = 0 and
cont> bitstring (rdb$flags from 1 for 1) = 1;
RDB$RELATION_NAME
CURRENT_JOB
CURRENT_SALARY
CURRENT_INFO
3 rows selected
SQL>
2 – CAST
The CAST function converts a value expression to another data
type. The source and target columns can be of any data type
except LIST OF BYTE VARYING.
If you convert to an INTERVAL data type, you must specify a
single interval qualifier field, and the source must be a numeric
value (fixed or floating) or a compatible INTERVAL data type.
For information on interval qualifiers, see the Data_Types HELP
topic.
You can also convert from a single interval qualifier field to a
numeric type (fixed or floating).
If you convert a TIMESTAMP literal using the CAST function, SQL
puts a separating space character (SQL92) between the date-body
and the time-body of the TIMESTAMP literal. For more information
on TIMESTAMP literals, see Date Time Literals.
The CAST function allows you to convert host language variables
into date-time values. You can also use the CAST function to
express dates in VMS format as ANSI format dates (using the
syntax CAST(date-vms-value-expr AS DATE ANSI)) to do date
arithmetic using DATE VMS data.
3 – CHARACTER_LENGTH
The CHARACTER_LENGTH (CHAR_LENGTH or LENGTH) function calculates
the length of a value expression of any data type.
If the result of the value expression is a character data type,
the CHARACTER_LENGTH function returns the length, in characters,
of the character string. (Remember that the length of a character
can be one or more octets.) If the result of the value expression
is NULL, the function returns a null value. You can use CHAR_
LENGTH or LENGTH as an alternative for CHARACTER_LENGTH.
4 – CONCAT
The CONCAT function returns the concatenated value expressions.
The result is a VARCHAR type large enough to hold all source
value expressions.
Any date/time or numeric values are implicitly converted to
VARCHAR types prior to concatenation. For dialects ORACLE LEVEL1
and ORACLE LEVEL2, any supplied value that is NULL is ignored.
For all other dialects, in conformance with the ANSI and ISO SQL
database language standard, the resulting CONCAT expression is
NULL.
CONCAT is functionally equivalent to the concatenation operator
(||).
Example: Using the CONCAT function
SQL> select distinct CONCAT (e.last_name, ' has a ', d.degree, ' degree')
cont> from employees e, degrees d
cont> where e.employee_id = d.employee_id
cont> limit to 5 ROWS;
Ames has a MA degree
Ames has a PhD degree
Andriola has a MA degree
Andriola has a PhD degree
Babbin has a MA degree
5 rows selected
5 – CONCAT_WS
The CONCAT_WS function returns the concatenated value expression
using the first parameter as a separator which is applied after
each of the other parameters.
If the separator value expression resolves to NULL then the
result of CONCAT_WS will be NULL. If any other parameter value
expression resolves to NULL then it will be ignored. That is,
that column value and any separator will not be included in the
output.
The function CONCAT_WS accepts all data types with the exception
of LIST OF BYTE VARYING, LONG, and LONG RAW. Each non-character
string value will be implicitly converted to VARCHAR with a size
appropriate for the data type.
The result of this function will have the type VARCHAR with a
length long enough for the concatenated data and separators.
If dialect ORACLE LEVEL1 or ORACLE LEVEL2 is used then zero
length strings ('') will be considered as NULL and so be excluded
from the output. If the resulting value is a zero length string
then the result of CONCAT_WS will be NULL.
Example: Using the CONCAT_WS function to simplify the formatting
of table data in CSV (comma separated value) format.
SQL> select '"' ||
cont> CONCAT_WS ('", "', first_name, nvl(middle_initial,''), last_name)
cont> || '"'
cont> from employees
cont> order by employee_id;
"Alvin ", "A", "Toliver "
"Terry ", "D", "Smith "
"Rick ", "", "Dietrich "
"Janet ", "", "Kilpatrick "
. . .
"Peter ", "", "Blount "
"Johanna ", "P", "MacDonald "
"James ", "Q", "Herbener "
100 rows selected
SQL>
6 – CONVERT
The CONVERT function converts a character string to the specified
character set.
You cannot specify the source character set as you can with
Oracle. The destination character set must be a character set
supported by Oracle Rdb.
The CONVERT function is functionally equivalent to the
TRANSLATE...USING function.
Example: Using the CONVERT function
SQL> SELECT CONVERT (english, RDB$SHIFT_JIS)
cont> FROM colours;
Black
White
Blue
Red
Yellow
Green
6 rows selected
7 – CURRENT_DATE
The CURRENT_DATE function returns a DATE data type value (ANSI
format) containing year, month, and day for date 'today'. You
can specify an optional fractional-seconds precision for CURRENT_
DATE.
Example: Using the CURRENT_DATE function
The following example shows how a site with an Oracle Rdb
database might use the CURRENT_DATE function to determine
employee ages. You must use the CAST function to convert the
DATE column BIRTHDAY from VMS to ANSI format to use it with the
ANSI format CURRENT_DATE function.
SQL> ATTACH FILENAME 'corporate_data';
SQL> SET SCHEMA 'ADMINISTRATION.PERSONNEL';
SQL> CREATE VIEW AGE (LAST_NAME, FIRST_NAME, BIRTHDAY, AGE)
cont> AS SELECT LAST_NAME, FIRST_NAME, BIRTHDAY,
cont> (CURRENT_DATE - CAST(BIRTHDAY AS DATE ANSI)) YEAR TO MONTH
cont> FROM EMPLOYEES ORDER BY BIRTHDAY ASC LIMIT TO 10 ROWS;
SQL> --
SQL> -- A SELECT statement displays the ten oldest employees.
SQL> SELECT * FROM AGE;
LAST NAME FIRST_NAME BIRTHDAY AGE
O'Sullivan Rick 12-Jan-1923 68-06
Clairmont Rick 23-Dec-1924 66-07
Nash Walter 19-Jan-1925 66-06
Kinmonth Louis 7-Apr-1926 65-03
Bartlett Dean 5-Mar-1927 64-06
Johnson Bill 13-Apr-1927 64-03
Herbener James 28-Oct-1927 63-09
Babbin Joseph 12-Dec-1927 63-07
Ziemke Al 27-Oct-1928 62-09
Reitchel Charles 13-Dec-1928 62-07
10 rows selected
SQL>
8 – CURRENT_TIME
The CURRENT_TIME function returns a TIME data type value
containing hours, minutes, and seconds for time 'now'.
You can specify a fractional precision between 0 and 2 for
the seconds returned by CURRENT_TIME. The fractional-seconds
precision is a number that designates the number of digits
returned in the field. For example, a fractional precision of
2 means that seconds are returned as hundredths of a second (2
digits beyond the decimal point), while a fractional precision of
1 means that only tenths of a second are returned (1 digit beyond
the decimal point).
You can specify a current default for a time or timestamp field
with nondefault fractional-seconds precision.
9 – CURRENT_TIMESTAMP
The CURRENT_TIMESTAMP function returns a TIMESTAMP data type
value containing year, month, and, day for date 'today' and
hours, minutes, and seconds for time 'now'.
As in CURRENT_TIME, you can specify a fractional precision
between 0 and 2 for the seconds returned by CURRENT_TIMESTAMP.
The fractional-seconds precision is a number that designates the
number of digits returned in the field.
The CURRENT_TIMESTAMP data type can be either DATE VMS or DATE
ANSI format. Date-time arithmetic is not allowed with DATE VMS
columns. A DATE VMS format CURRENT_TIMESTAMP specifies the day,
month, and year of the current date and the hours, minutes, and
seconds of the current time. A DATE ANSI format CURRENT_TIMESTAMP
specifies the year, month and day of the current date, followed
by the hours, minutes, and seconds of the current time.
10 – CURRENT_UID
The CURRENT_UID function returns a unique integer that represents
the current user. This UID value is based on the AUTHORIZATION
user or role for the currently executing stored routine, or the
SESSION_USER if there is no current authorization.
11 – CURRENT_USER
The CURRENT_USER function returns the current active user name
for a request.
If a definer's rights request is executing, the CURRENT_USER
function returns the rights identifier of the module definer. If
a definer's rights request is not executing, CURRENT_USER returns
the session user name, if it exists. Otherwise, CURRENT_USER
returns the system user name. See Authorization Identifiers for
more information.
The resulting data type is CHAR(31).
The CURRENT_USER function does not return the definer's user name
of a trigger.
Example: Using the CURRENT_USER function
Example 1: To allow users access only to the rows they inserted,
create a view
SQL> CREATE VIEW SELECTIVE_EMPLOYEES_UPDATE AS
cont> SELECT * FROM EMPLOYEES
cont> WHERE USER_ID = CURRENT_USER
cont> WITH CHECK OPTION CONSTRAINT MUST_HAVE_USER;
12 – EXTRACT
The EXTRACT function returns a single date-time field expressed
as an integer from a column of data type DATE, TIME, TIMESTAMP,
or INTERVAL.
The date-time fields that EXTRACT can return are:
o YEAR
o MONTH
o DAY
o HOUR
o MINUTE
o SECOND
o WEEKDAY
o JULIAN
o WEEK_NUMBER
o YEAR_WEEK
The data type returned is a signed longword of scale 0, unless
the date-time field is SECOND. If the SECOND field is selected,
then the scale is set to 2.
If you specify WEEKDAY, you can only use the data types TIMESTAMP
and DATE as the extract source. In all other cases, the extract
source can be data type DATE, TIME, TIMESTAMP, or INTERVAL. If
you specify WEEKDAY, then the EXTRACT function returns an integer
representing the day of the week. (Monday is represented as day
1, Sunday as day 7.)
If the EXTRACT function is applied to a null value, it returns a
null value.
The number of days since the first day of a year, called
the Julian date, can be an important integer value to which
programmers need direct access. The SQL EXTRACT function lets you
determine the Julian date from column data defined with date-time
data types.
The JULIAN keyword requires that the extract expression resolve
to either the DATE ANSI or TIMESTAMP date-time data type. Value
expressions that do not resolve to one of these particular data
types will fail. For example, trying to extract the Julian date
from an expression defined by the CURRENT_TIME data type results
in the following SQL error message:
SQL> SELECT EXTRACT(JULIAN FROM CURRENT_TIME) FROM ACCOUNTING.DAILY_HOURS;
%RDB-F-CONVERT_ERROR, invalid or unsupported data conversion
-RDMS-E-EXT_JULIAN_TS, invalid type for EXTRACT JULIAN, must be DATE or
TIMESTAMP
SQL>
You cannot represent dates from the year 1858 using the JULIAN
keyword in the EXTRACT function because JULIAN calculates from
1-January and the first date in 1858 is 18-November.
The options WEEK_NUMBER and YEAR_WEEK return the week number
as defined by the International Standard ISO 8601:1988 "Data
elements and interchange formats - Information interchange -
Representation of dates and times".
WEEK_NUMBER is a number between 1 and 53 representing the week of
the year (most years only have 52 weeks). A week starts on Monday
and has most of its days falling in a specific year.
YEAR_WEEK is a variation of the WEEK_NUMBER that includes the
year (including the century) in which the week logically falls.
The values range from 185901 through 999952 (higher values are
possible if dates are constructed with a year beyond 9999). The
last two digits of the value are identical to the value returned
by the WEEK_NUMBER option.
13 – LENGTH
The LENGTH function returns the length of a supplied string in
characters. Also see the CHARACTER_LENGTH function.
14 – LENGTHB
The LENGTHB function returns the length of a supplied string in
bytes. Also see the OCTET_LENGTH function.
15 – LOCALTIME
The LOCALTIME built-in function is a synonym for CURRENT_TIME,
and is defined by the SQL:1999 database language standard.
16 – LOCALTIMESTAMP
The LOCALTIMESTAMP built-in function is a synonym for CURRENT_
TIMESTAMP and is defined by the SQL:1999 database language
standard.
17 – LOWER
The LOWER function converts all uppercase characters in a value
expression to lowercase characters. This function is useful to
maintain consistency in value expressions in the database.
If the result of the value expression is NULL, the function
returns a null value.
When you use the LOWER function, SQL follows the rules of
the character set for the value expression when converting
characters to lowercase. For example, if the character set of
the value expression is Hanzi and ASCII, SQL converts only the
ASCII characters to lowercase. It does not convert the Hanzi
characters.
18 – OCTET_LENGTH
The OCTET_LENGTH (or LENGTHB) function calculates the length, in
octets, of a value expression of any data type.
If the result of the value expression is NULL, the function
returns a null value. Otherwise, the function returns the length,
in octets, of the value expression. You can use LENGTHB as an
alternative for OCTET_LENGTH.
19 – POSITION
The POSITION function searches for a string in a character value
expression. The first character value expression is also called
a search string. The second character value expression is also
called a source string. If the search string is located, the
POSITION function returns a numeric value that indicates the
position of the search string in the source string. The returned
numeric value is the absolute position of the search string in
the source string starting with 1. The match between the search
string and the source string is case sensitive.
If the search string is not found in the source string, the
POSITION function returns a zero (0) value. If any of the
strings is NULL, the result is NULL.
The FROM clause of the POSITION function is an extension to the
ANSI/ISO SQL standard and allows searching to begin from any
location.
Examples: Using the POSITION function
Example 1: Using the POSITION function in a SELECT statement
SQL> SELECT COLLEGE_NAME,
cont> POSITION ('University' IN COLLEGE_NAME)
cont> FROM COLLEGES
cont> WHERE COLLEGE_NAME LIKE '_%University%';
COLLEGE_NAME
American University 10
Drew University 6
Harvard University 9
Purdue University 8
Stanford University 10
Yale University 6
6 rows selected
Example 2: Using the POSITION function with the SUBSTRING clause
SQL> SELECT SUBSTRING (COLLEGE_NAME FROM 1 FOR
cont> POSITION ('University' IN COLLEGE_NAME) -1)
cont> FROM COLLEGES
cont> WHERE COLLEGE_NAME LIKE '_%University%';
American
Drew
Harvard
Purdue
Stanford
Yale
6 rows selected
Example 3: Using the POSITION function to find individual words.
Because this example uses the TRACE statement, you must define
the RDMS$DEBUG_FLAGS logical name to "Xt".
SQL> BEGIN
cont> DECLARE :TXT VARCHAR(100);
cont> DECLARE :RES VARCHAR(20);
cont> DECLARE :ST, :EN INTEGER;
cont> --
cont> SET :TXT = 'Some words and phrases';
cont> --
cont> -- Start at the beginning
cont> --
cont> SET :ST = 1;
cont> --
cont> -- Loop over all the text looking for space delimiters
cont> --
cont> WHILE :ST <= CHAR_LENGTH(:TXT)
cont> LOOP
cont> SET :EN = POSITION (' ' IN :TXT FROM :ST);
cont> IF :EN = 0 THEN
cont> --
cont> -- No trailing spaces, so assume space after last character
cont> --
cont> SET :EN = CHAR_LENGTH(:TXT) + 1;
cont> END IF;
cont> SET :RES = SUBSTRING(:TXT FROM :ST FOR :EN - :ST);
cont> IF CHAR_LENGTH (TRIM (:RES)) > 0 THEN
cont> --
cont> -- Have a word to display
cont> --
cont> TRACE 'Word: "', :RES, '"';
cont> END IF;
cont> --
cont> -- Advance the start position
cont> --
cont> SET :ST = :EN + 1;
cont> END LOOP;
cont> END;
~Xt: Word: "Some "
~Xt: Word: "words "
~Xt: Word: "and "
~Xt: Word: "phrases "
20 – ROUND
The ROUND function accepts two numeric value expressions (n,m) as
arguments. The result is the first value expression n rounded to
m places to the right of the decimal point. The value expression
m can be negative to round off digits to the left of the decimal
point. The data type of m must be an unscaled numeric value
(tinyint, smallint, integer, or bigint). If omitted, m defaults
to zero. See the following example.
SQL> select avg (salary_amount)
cont> from salary_history
cont> where employee_id = '00164';
3.857350000000000E+004
1 row selected
SQL> select round (avg (salary_amount)) as SAL edit using 'sz(9).99'
cont> from salary_history
cont> where employee_id = '00164';
SAL
38574.00
1 row selected
Usage Notes
o The function ROUND for numeric values is supported as a native
function in Oracle Rdb.
o Fixed point values are truncated and rounded correctly.
Floating values, while supported by ROUND, may not always
return the expected results. Please review usage of ROUND in
such contexts.
o The result type for ROUND will match the data type of the
input source parameter.
o The implementation of ROUND for DATE values requires the use
of the OCI Services for Rdb library (also know as SQL*net for
Rdb). These functions will now accept DATE ANSI, TIMESTAMP and
DATE VMS values.
o Attempts to use ROUND on a database that is not setup for OCI
Services will receive errors similar to these:
SQL> select TRUNC (current_date) from rdb$database;
%RDB-E-OBSOLETE_METADA, request references metadata objects that no longer exist
-RDMS-F-BAD_SYM, unknown routine symbol - TRUN2
SQL> select ROUND (current_date) from rdb$database;
%RDB-E-OBSOLETE_METADA, request references metadata objects that no longer exist
-RDMS-F-BAD_SYM, unknown routine symbol - ROUN2
NOTE
The special functions ROUN2 and TRUN2 are internal
routines to deal with DATE types.
o ROUND supports the data types REAL, FLOAT and DOUBLE PRECISION
for both parameter and results. However, due to the imprecise
nature of floating point arithmetic this may cause unexpected
results. A value such as 4.185 will not round to 4.19 as
expected because the internal (and approximate) representation
of the number is something like 4.184999942780E+000 and
therefore does not appear to require rounding to the second
decimal place according to the rounding rules.
The following example shows this problem.
SQL> select cast(round (4.185,2) as integer(2)) from rdb$database;
4.18
1 row selected
SQL> select cast(round (4.175,2) as integer(2)) from rdb$database;
4.18
1 row selected
SQL>
NOTE
The result of a divide operation (/) or the AVG, STDDEV,
VARIANCE statistical functions are floating point values,
so applying TRUNC or ROUND to those results, even if
performed on integer sources, will also be affected by
the intermediate floating point type.
o If you use SQL to access older versions of Rdb (such as via
remote access) then SQL will revert to the previous behavior
and use the SQL functions provided by the SQL_FUNCTIONS
library.
21 – SESSION_UID
Returns a unique integer that represents the session user.
22 – SESSION_USER
The SESSION_USER function returns the current active session user
name.
If the session user name is not returned, the system user name is
returned. The resulting data type is CHAR(31).
23 – SIZEOF
The SIZEOF (or VSIZE) function calculates the maximum length,
in octets, of a value expression of any data type. It returns
the length or storage width of the value expression. Like OCTET_
LENGTH, SIZEOF returns the number of eight-bit units (octets)
rather than the number of characters if the expression yields a
text string in a multi-byte character set. You can use VSIZE as
an alternative for SIZEOF.
24 – SUBSTRING
Substrings return portions of character value expressions. A
substring must have the data type CHAR, VARCHAR, LONG VARCHAR,
NCHAR, or NCHAR VARYING.
To specify a substring, you must specify the value expression
and the FROM keyword, followed by the start position of the value
expression. (The first character in the string occupies position
1.) You can optionally add a FOR clause after the FROM clause
to specify the length of the value expression after the start
position.
The start position and string length values can be a numeric
value expression. By default, SQL expects the start position and
the string length to be specified in octets. You can use the SET
DIALECT or the SET CHARACTER LENGTH statements or the DIALECT
or CHARACTER LENGTH clause of the SQL module language header and
DECLARE MODULE statement to specify whether the length value is
octets or characters.
If you specify a length longer than the string, SQL returns
only valid characters in the string and terminates the returned
substring after the last valid character.
If either operand of the substring is a null value, the resulting
value is also null.
When you use a substring with the equal (=) conditional
operator, the operation is case sensitive.
25 – SYS_GET_DIAGNOSTIC
The SYS_GET_DIAGNOSTIC function returns the same session
information available to the GET DIAGNOSTICS statement. This
function provides a shorthand method of fetching values without
requiring a compound statement, or an intermediate variable.
Syntax: SYS_GET_DIAGNOSTIC ( statement-item-name ) ->
For the list of keywords acceptable by this function please
see the statement-item-name syntax under the GET DIAGNOSTICS
statement.
Each keyword used with SYS_GET_DIAGNOSTIC will cause the result
to have a different associated data type. Please refer to the GET
DIAGNOSTICS statement for the returned data types.
The following example shows the return of session information.
SQL> select SYS_GET_DIAGNOSTIC (CONNECTION_NAME) as CONN,
cont> SYS_GET_DIAGNOSTIC (SERVER_IDENTIFICATION) as IDENT,
cont> SYS_GET_DIAGNOSTIC (DATABASE_HANDLE) as DBHANDLE
cont> from GET_DIAG;
CONN IDENT DBHANDLE
RDB$DEFAULT_CONNECTION Oracle Rdb V7.2-501 1
1 row selected
SQL>
26 – SYS_GUID
The SYS_GUID function returns a 16 octet globally unique
identifier. Applications would use this to provide unique values
from various applications and across databases in an OpenVMS
cluster or network.
This function uses the OpenVMS system service SYS$CREATE_UID.
Applications that call this system service create compatible
values for Rdb.
The returned value from SYS_GUID() may contain octets that
are zero. If returning values to C applications, then Oracle
recommends using the $SQL_VARCHAR pseudo type to avoid C null
terminated string semantics.
The SYS_GUID() returns data using a special character set. This
special character set is used by Oracle Rdb to distinguish this
type of string from others. Interactive SQL will format the value
using standard OpenVMS formatting services when this character
set is seen. Note that these services perform reordering of the
octet values during formatting, that is, the value isn't a direct
hexadecimal representation of the value.
Database administrators can define a domain to be used by
applications which will make it easier to use.
SQL> create domain GUID_DOMAIN
cont> char(16) character set -11;
SQL>
SQL show domain GUID_DOMAIN;
GUID_DOMAIN CHAR(16)
GUID 16 Characters, 16 Octets
SQL>
This domain can be used for column, parameter, and variable
definitions.
To support storing literal GUID values, SQL also supports GUID
literals. The literals follow the standard literal format using
the special prefix _GUID, as shown in the following examples.
SQL> create domain GUID_DOMAIN
cont> char(16) character set -11;
SQL> show domain GUID_DOMAIN;
GUID_DOMAIN CHAR(16)
GUID 16 Characters, 16 Octets
SQL> create table SAMPLE
cont> (a int
cont> ,b GUID_DOMAIN default _guid'00000000-0000-0000-0000-000000000000');
SQL> insert into SAMPLE default values;
1 row inserted
SQL> show table (column) SAMPLE;
Information for table SAMPLE
Columns for table SAMPLE:
Column Name Data Type Domain
----------- --------- ------
A INTEGER
B CHAR(16) GUID_DOMAIN
GUID 16 Characters, 16 Octets
Oracle Rdb default: GUID'00000000-0000-0000-0000-000000000000'
SQL>
The literal can also be used in queries to select existing rows.
SQL> select * from SAMPLE
cont> where b = _guid'3DBB657F-8513-11DF-9B74-0008029189E7';
27 – SYSDATE
The SYSDATE function returns the current date and time. It does
not require any arguments.
SYSDATE is a synonym for CURRENT_TIMESTAMP. As with CURRENT_
TIMESTAMP, the return result of SYSDATE is affected by the
setting of the SET DEFAULT DATE FORMAT statement, as shown in
the following example:
SQL> SET DEFAULT DATE FORMAT 'SQL99'
SQL> SELECT SYSDATE, CURRENT_TIMESTAMP
cont> FROM RDB$DATABASE;
1995-08-21 15:21:05.29 1995-08-21 15:21:05.29
1 row selected
SQL> SET DEFAULT DATE FORMAT 'VMS'
SQL> SELECT SYSDATE, CURRENT_TIMESTAMP
cont> FROM RDB$DATABASE;
21-AUG-1995 15:21:24.83 21-AUG-1995 15:21:24.83
1 row selected
28 – SYSTIMESTAMP
The SYSTIMESTAMP function returns the current date and time as a
TIMESTAMP type. This function is similar to SYSDATE and CURRENT_
TIMESTAMP however, its type doesn't change when the SET DEFAULT
DATE FORMAT command is used.
Syntax: SYSTIMESTAMP [ ( fractional-seconds-precision ) ]
The function name can be followed by an optional fractional-
seconds-precision. This value, if omitted, defaults to 2 and
accepts the values 0, 1, or 2.
The following example shows that SYSTIMESTAMP always returns a
SQL standard date and time.
SQL> select systimestamp,sysdate,current_timestamp from rdb$database;
2007-03-27 16:33:32.19 27-MAR-2007 16:33:32.19 27-MAR-2007 16:33:32.19
1 row selected
SQL> set default date format 'sql99';
SQL> select systimestamp,sysdate,current_timestamp from rdb$database;
2007-03-27 16:33:41.32 2007-03-27 16:33:41.32 2007-03-27 16:33:41.32
1 row selected
SQL>
29 – SYSTEM_UID
Returns a unique integer that represents the system user.
30 – SYSTEM_USER
The SYSTEM_USER function returns the user name of the process at
the time of the database attach.
If you attach to the database specifying a user name and password
in the USER and USING clauses, SQL returns the user name you
specify.
The resulting data type is CHAR(31).
31 – TRANSLATE
SQL provides an alterative TRANSLATE function which uses a comma
separated list of arguments.
TRANSLATE ( <sourcestring>, <fromstring>, <tostring> )
This format of the TRANSLATE function uses two translation
character strings to define the translation of specific
characters. Any characters in the <sourcestring> which do not
appear in the <fromstring> are not replaced. Any characters
in the source string which do appear in the <fromstring> are
replaced with the character from the corresponding position
in the <tostring>. The <fromstring> may be longer than the
<tostring> and in this case the matched character is omitted
from the result.
If any of the arguments <sourcestring>, <fromstring>, and
<tostring> are NULL then the result of the TRANSLATE function
is NULL.
The data type of the result is a VARCHAR string with a length and
character set equivalent to that of the <sourcestring>.
Examples
Example 1: Eliminating characters
This example removes the single quote punctuation character
from names. For example, "O'Hara" will becomes "OHara". This
would usually be done during sorting to avoid having the single
quote group these names separately, and instead they are ordered
between names starting with "Og" (such as Ogdan) and "Oi" (such
as Oiler).
SQL> select last_name
cont> from EMPLOYEES
cont> order by TRANSLATE (last_name, '"''', '"');
NOTE
In Oracle RDBMS the empty string is considered to be NULL,
so an extra '"' character was added to the translation
strings to avoid a NULL result. This is not required for
Oracle Rdb. However, if an ORACLE dialect is used then these
Oracle semantics would be possible.
Example 2: Formatting characters
When numeric values are displayed they are normally displayed
with leading spaces, however, some applications require leading
zeros. This example assumes that the postal code is stored as an
INTEGER but needs to report the 5 digits with leading zeros.
SQL> select TRANSLATE (CAST(postal_code as CHAR(5)), ' ', '0')
cont> from EMPLOYEES;
Example 3: Masking characters
TRANSLATE can be used to mask out characters which should not
appear in the output. For instance, when displaying a license
number all the letters are required to be converted to 'X' and
all digits to '9'.
SQL> select TRANSLATE ('2KRW229',
cont> '01234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ',
cont> '99999999999XXXXXXXXXXXXXXXXXXXXXXXXXX')
cont> from ...;
9XXX999
Example 4: Transforming text for sorting
Last names often contain special punctuation characters, such as
the single quote in O'Sullivan, or D'Amico. Consider this simple
example from the PERSONNEL database.
SQL> select last_name
cont> from employees
cont> where last_name starting with 'D'
cont> order by last_name;
LAST_NAME
D'Amico
Dallas
Danzig
Dement
Dement
Dietrich
Dietrich
7 rows selected
SQL>
You can see that the quote punctuation causes the name to sort
higher than expected by many applications (such as telephone book
listings).
This example removes the single quote punctuation character from
names and converts the last name to lowercase so that these names
sort within similar names without quote punctuation. However, the
original name is displayed as stored in the column.
SQL> select last_name
cont> from employees
cont> where last_name starting with 'D'
cont> order by translate (last_name,
cont> 'ABCDEFGHIJKLMNOPQRSTUVWXYZ''',
cont> 'abcdefghijklmnopqrstuvwxyz');
LAST_NAME
Dallas
D'Amico
Danzig
Dement
Dement
Dietrich
Dietrich
7 rows selected
SQL>
The first string argument to TRANSLATE contains the target set
of characters, if a character in the name does not match this set
it is written to the result unchanged. For example, the trailing
spaces are simply copied to the result.
The second string argument contains the translations for those
characters. Any upper case characters are transformed into
their lower case equivalent. If a single quote character is
matched then it is omitted from the result because there was
no translation.
Applying these rules will convert "D'Amico" to "damico" so that
it sorts between "dallas" and "danzig".
32 – TRANSLATE_USING
The TRANSLATE function translates a character value expression
from one character set to another compatible character set.
The characters in the char-value-expr are translated, character-
by-character, to the character set indicated by the translation
name. If a direct translation exists for a character, it is
replaced by the equivalent character in the translation character
set. If there is no direct translation for a character, it is
replaced by the space character of the translation character set,
as shown in the example using the TRANSLATE function.
For example, the Kanji character set contains traditional
Kanji characters, Katakana characters, ASCII characters,
and Roman characters that are ASCII characters encoded in 2
octets. If a Kanji char-value-expr is translated using the
RDB$KATAKANA translation name, those 2-octet Kanji characters
that have an equivalent in the 1-octet Katakana character set
are translated. The other characters are replaced by the Katakana
space character.
If a character in the source character string is not compatible
with the target character set, SQL substitutes a space character
for that character.
33 – TRIM
The TRIM function removes either or both leading or trailing
spaces, numbers, or characters from any character value
expression. SQL returns the specified string minus any leading
or trailing characters (or both).
The BOTH option is the default if none is specified. The space
character is the default if a string is not specified.
The character value expression that you trim must be defined
as data type CHAR, VARCHAR, NCHAR, or NCHAR VARYING. Use the
CAST function to convert other data types before using the TRIM
function.
SQL returns a run-time error when the trim character is not
exactly one character in length.
Examples: Using the TRIM function
Example 1: The following example, though not effective, shows the
TRIM function:
SQL> SELECT LAST_NAME,
cont> TRIM (LEADING 'H' FROM LAST_NAME)
cont> FROM EMPLOYEES
cont> WHERE LAST_NAME LIKE 'H%';
LAST_NAME
Hall all
Harrington arrington
Harrison arrison
Hastings astings
Herbener erbener
5 rows selected
Example 2: Using the TRIM function with the WHERE clause
SQL> -- The following INSERT statement helps to show the
SQL> -- TRIM function.
SQL> --
SQL> INSERT INTO EMPLOYEES (LAST_NAME,FIRST_NAME,EMPLOYEE_ID) VALUES
cont> (' Hillson','Ann','99999');
1 row inserted
SQL> --
SQL> -- If you select columns without specifying the
SQL> -- TRIM function on the WHERE clause, SQL returns only those
SQL> -- last names that start with 'H' and have no leading spaces.
SQL> --
SQL> SELECT LAST_NAME || ', ' || FIRST_NAME
cont> FROM EMPLOYEES
cont> WHERE LAST_NAME LIKE 'H%';
Hall , Lawrence
Harrington , Margaret
Harrison , Lisa
Hastings , Norman
Herbener , James
5 rows selected
SQL> --
SQL> -- Add the TRIM function to the WHERE clause to get a complete
SQL> -- list of last names beginning with 'H' including those with
SQL> -- leading spaces.
SQL> --
SQL> SELECT LAST_NAME || ', ' || FIRST_NAME
cont> FROM EMPLOYEES
cont> WHERE TRIM (LEADING ' ' FROM LAST_NAME) LIKE 'H%';
Hastings , Norman
Harrington , Margaret
Hall , Lawrence
Harrison , Lisa
Hillson , Ann
Herbener , James
6 rows selected
Example 3: Using the TRIM function on the SELECT portion of a
query in addition to the WHERE clause
SQL> -- Add the TRIM function to the SELECT portion of the query
SQL> -- to trim the leading spaces from the display of 'Hillson'.
SQL> -- Note that the LEADING option has been changed to the BOTH
SQL> -- option to trim leading and trailing spaces from the
SQL> -- LAST_NAME column.
SQL> --
SQL> SELECT TRIM (BOTH ' ' FROM LAST_NAME) || ', ' || FIRST_NAME
cont> FROM EMPLOYEES
cont> WHERE TRIM (LEADING ' ' FROM LAST_NAME) LIKE 'H%';
Hastings, Norman
Harrington, Margaret
Hall, Lawrence
Harrison, Lisa
Hillson, Ann
Herbener, James
6 rows selected
34 – TRUNC
The TRUNC function accepts two numeric value expressions (n,m) as
arguments. The result is the first value expression n truncated
to m places to the right of the decimal point. The value
expression m can be negative to truncate digits to the left of
the decimal point. The data type of m must be an unscaled numeric
value (tinyint, smallint, integer, or bigint). If omitted, m
defaults to zero. See the following example.
SQL> select avg (salary_amount)
cont> from salary_history
cont> where employee_id = '00164';
3.857350000000000E+004
1 row selected
SQL> select trunc (avg (salary_amount)) as SAL edit using 'sz(9).99'
cont> from salary_history
cont> where employee_id = '00164';
SAL
38574.00
1 row selected
Usage Notes
o The function TRUNC for numeric values is supported as a native
function in Oracle Rdb.
o Fixed point values are truncated and rounded correctly.
Floating values, while supported by TRUNC, may not always
return the expected results. Please review usage of TRUNC in
such contexts.
o The result type for TRUNC will match the data type of the
input source parameter.
o The implementation of TRUNC for DATE values requires the use
of the OCI Services for Rdb library (also know as SQL*net for
Rdb). These functions will now accept DATE ANSI, TIMESTAMP and
DATE VMS values.
o Attempts to use TRUNC on a database that is not setup for OCI
Services will receive errors similar to these:
SQL> select TRUNC (current_date) from rdb$database;
%RDB-E-OBSOLETE_METADA, request references metadata objects that no longer exist
-RDMS-F-BAD_SYM, unknown routine symbol - TRUN2
SQL> select ROUND (current_date) from rdb$database;
%RDB-E-OBSOLETE_METADA, request references metadata objects that no longer exist
-RDMS-F-BAD_SYM, unknown routine symbol - ROUN2
NOTE
The special functions ROUN2 and TRUN2 are internal
routines to deal with DATE types.
o TRUNC supports the data types REAL, FLOAT and DOUBLE PRECISION
for both parameter and results. However, due to the imprecise
nature of floating point arithmetic this may cause unexpected
results. A value such as 4.185 will not round to 4.19 as
expected because the internal (and approximate) representation
of the number is something like 4.184999942780E+000 and
therefore does not appear to require rounding to the second
decimal place according to the rounding rules.
The following example shows this problem.
SQL> select cast(round (4.185,2) as integer(2)) from rdb$database;
4.18
1 row selected
SQL> select cast(round (4.175,2) as integer(2)) from rdb$database;
4.18
1 row selected
SQL>
NOTE
The result of a divide operation (/) or the AVG, STDDEV,
VARIANCE statistical functions are floating point values,
so applying TRUNC or ROUND to those results, even if
performed on integer sources, will also be affected by
the intermediate floating point type.
o If you use SQL to access older versions of Rdb (such as via
remote access) then SQL will revert to the previous behavior
and use the SQL functions provided by the SQL_FUNCTIONS
library.
35 – UPPER
The UPPER function converts all lowercase characters in a value
expression to uppercase characters. This function is useful to
maintain consistency in value expressions in the database.
If the result of the value expression is NULL, the function
returns a null value.
When you use the UPPER function, SQL follows the rules of
the character set of the value expression when converting
characters to uppercase. For example, if the character set of
the value expression is Hanzi and ASCII, SQL converts only the
ASCII characters to uppercase. It does not convert the Hanzi
characters.
36 – USER
The USER function specifies the current active user name for a
request and is a synonym for the (CURRENT_USER) function. For
definer's rights stored procedures, the returned user name is the
definer's user name. For all other requests, it is the current
user name of the calling routine or, if no calling routine, the
current session user name. The resulting data type is CHAR(31).