1 Built_In_Functions 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. 2 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. 2 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. 2 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 2 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> 2 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 2 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> 2 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. 2 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. 2 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. 2 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; 2 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. 2 LENGTH The LENGTH function returns the length of a supplied string in characters. Also see the CHARACTER_LENGTH function. 2 LENGTHB The LENGTHB function returns the length of a supplied string in bytes. Also see the OCTET_LENGTH function. 2 LOCALTIME The LOCALTIME built-in function is a synonym for CURRENT_TIME, and is defined by the SQL:1999 database language standard. 2 LOCALTIMESTAMP The LOCALTIMESTAMP built-in function is a synonym for CURRENT_ TIMESTAMP and is defined by the SQL:1999 database language standard. 2 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. 2 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. 2 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 " 2 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. 2 SESSION_UID Returns a unique integer that represents the session user. 2 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). 2 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. 2 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. 2 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> 2 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'; 2 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 2 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> 2 SYSTEM_UID Returns a unique integer that represents the system user. 2 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). 2 TRANSLATE SQL provides an alterative TRANSLATE function which uses a comma separated list of arguments. TRANSLATE ( , , ) This format of the TRANSLATE function uses two translation character strings to define the translation of specific characters. Any characters in the which do not appear in the are not replaced. Any characters in the source string which do appear in the are replaced with the character from the corresponding position in the . The may be longer than the and in this case the matched character is omitted from the result. If any of the arguments , , and 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 . 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". 2 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. 2 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 2 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. 2 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. 2 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).