Call (800) 766-1884 for Oracle support & training
Free Oracle Tips


Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance
 

Free Oracle Tips


 

HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

 

 

   
 

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.

  
 

Oracle performance tuning software 
 
 
 
 

Oracle performance tuning book

 

 
 
 
Oracle performance Tuning 10g reference poster
 
 
 
Oracle training in Linux commands
 
Oracle training Excel
 
Oracle training & performance tuning books
 

 

   

Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter