 |
|
EnterpriseDB: SQL Functions
Oracle Tips by
Burleson
|
By SQL
Function, I am referring to the SQL functions that come with
EnterpriseDB. Many of these functions can also be called from SPL but
all of them can be called from SQL. This is not meant to be an
exhaustive list. These are the most commonly called functions and the
ones you are most likely to run into while working with EnterpriseDB
applications.
Conversion Functions
Conversion functions use the concept of a format mask. A format mask
is a text string that is passed to the conversion function that
directs the function how to format the output.
Table
3.2 lists the most common DATE format masks. Additional, less common
format masks can be found in the EnterpriseDB documentation.
|
FORMAT |
DESCRIPTION |
|
AD |
AD
indicator (Can be shown with or without periods) |
|
AM |
12-hour clock morning hours (Can be shown with or without periods) |
|
BC |
BC
indicator (Can be shown with or without periods) |
|
CC |
Century Indicator |
|
D |
Day
of week (1-7) |
|
DAY |
Name
of day, padded with blanks to length of 9 characters |
|
DD |
Day
of month (1-31) |
|
DDD |
Day
of year (1-366) |
|
DY |
Abbreviated name of day |
|
HH |
Hour
of day (1-12) |
|
HH12 |
Hour
of day (1-12) |
|
HH24 |
Hour
of day (0-23) |
|
J |
Julian day; the number of days since
January 1, 4712
BC. Number specified with "J" must be integers |
|
MI |
Minute (0-59) |
|
MM |
Month (01-12; JAN = 01) |
|
MON |
Abbreviated name of month |
|
MONTH |
Name
of month, padded with blanks to length of 9 characters |
|
PM |
12-hour clock evening hours (Can be shown with or without periods) |
|
Q |
Quarter of year (1, 2, 3, 4; JAN-MAR = 1) |
|
RR |
Given a year with 2 digits: If the year is <50 and the last 2
digits of the current year are >=50, then the first 2 digits of
the returned year are 1 greater than the first 2 digits of the
current year. If the year is >=50 and the last 2 digits of the
current year are <50, then the first 2 digits of the returned year
are 1 less than the first 2 digits of the current year |
|
RRRR |
Round year. Accepts either 4-digit or 2-digit input. If 2-digit,
provides the same return as RR. If you don't want this
functionality, then simply enter the 4-digit year |
|
SS |
Second (0-59) |
|
SSSSS |
Seconds past
midnight
(0-86399) |
|
WW |
Week
of year (1-53) where week 1 starts on the first day of the year
and continues to the seventh day of the year |
|
W |
Week
of month (1-5) where week 1 starts on the first day of the month
and ends on the seventh |
|
YEAR |
Year, spelled out |
|
YYYY |
4-digit year, (can have a comma Y,YYY) |
Table
3.2: Common DATE Format Masks
Table
3.3 lists the most common NUMERIC format masks. Additional, less
common format masks can be found in the EnterpriseDB documentation.
|
FORMAT |
DESCRIPTION |
|
FM |
Suppress spaces |
|
9 |
value with the specified number of digits with a leading space if
positive or with a leading minus if negative. |
|
0 |
value with leading/trailing zeros |
|
$ |
value with a leading dollar sign |
|
MI |
places a trailing minus sign (-) with a negative value |
|
D |
specifies the location of a decimal point in the returned value |
|
G |
specifies the location of the group separator in the returned
value |
|
L |
currency symbol (uses locale) |
|
D |
decimal point (uses locale) |
|
,
(comma) |
returns a comma in the specified position. |
|
.
(period) |
returns a decimal point, which is a period (.) in the specified
position |
Table
3.3: Common NUMERIC Format Masks
TO_CHAR:
Convert a DATE to a VARCHAR2:
*
Parameter 1 = Date or Timestamp
* Parameter 2 = Format Mask
* Returns = VARCHAR2
TO_CHAR:
Convert a NUMBER to a VARCHAR2:
*
Parameter 1 = Number or Floating Point Number
* Parameter 2 = Format Mask
* Returns = VARCHAR2
TO_DATE:
Convert a VARCHAR2 to a TIMESTAMP (Redwood Date):
*
Parameter 1 = VARCHAR2
* Parameter 2 = Format Mask
* Returns = TIMESTAMP
TO_TIMESTAMP: Convert a VARCHAR2 to a TIMESTAMP (Redwood Date):
*
Parameter 1 = VARCHAR2
* Parameter 2 = Format Mask
* Returns = TIMESTAMP
TO_NUMBER: Convert a VARCHAR2 to a NUMBER:
*
Parameter 1 = VARCHAR2
* Parameter 2 = Format Mask
* Returns = NUMBER
Conversion Examples:
SELECT ename,
to_char(sal, 'FM L9G999G990D00') salary,
to_char(hiredate, 'FM Day, DD MONTH, YEAR') hire_Date
FROM emp
WHERE ename = 'MARTIN';
edb=# SELECT ename,
edb-# to_char(sal, 'FM L9G999G990D00') salary,
edb-# to_char(hiredate, 'FM Day, DD MONTH, YEAR') hire_Date
edb-# FROM emp
edb-# WHERE ename = 'MARTIN';
ename | salary |
hire_date
--------+------------+----------------------------------------------------------- MARTIN
| $1,250.00 | Monday, 28 SEPTEMBER, ONE THOUSAND NINE HUNDRED EIGHTY
ONE
(1 row)
edb=#
SELECT to_date( '01-JUN-2007 22:18:55', 'DD-MON-YYYY
HH24:MI:SS') date_field,
To_number( '$1,900', '$9,9999')
FROM DUAL;
edb=# SELECT to_date( '01-JUN-2007 22:18:55',
edb=# 'DD-MON-YYYY HH24:MI:SS') date_field,
edb-# To_number( '$1,900', '$9,9999')
edb-# FROM DUAL;
date_field | to_number
--------------------+-----------
01-JUN-07 22:18:55 | 1900
(1 row)
edb=#
This
is an excerpt from the book "EnterpriseDB:
The Definitive Reference" by Rampant TechPress. |
