sql database - Online Tech Support
  Home
 

This tutorial page is based on examples to be easier to follow. The Oracle TO_CHAR function allows converting others’ data types value to character type and function To_Char always returns VARCHAR2 type. You can convert to VARCHAR2 for example DATE, NUMBER, NCHAR, NVARCHAR2, CLOB, or NCLOB data types. The TO_CHAR syntax is following:

TO_CHAR(<datatype>[,<data_mask>])

Take a look at the examples below to see how works Oracle TO_CHAR. For example the first query is number “01234” without To_Char function and the nature of this type is getting trimmed by Oracle database so the SQL output returns “1234“.

SELECT 01234 
  FROM DUAL;

online tech support online computer help computer technician computer problems computer oracle to_char oracle to_char in oracle sql to_char oracle sql to_char in oracle sql database sql retirement planning retirement sql database programming database oracle retirement planning retirement sql database oracle database sql

The reason why Oracle database removed the first 0 (zero) is to keep the database tidy and that way the number looks less confusing for users. Having a zero in front of 1234 doesn’t make much sense either so it gets cleaned away.

Now to keep the number as with the original look, you should use Oracle To_Char function and store the value into Oracle database table as VARCHAR2 type because numbers like phone numbers or credit card numbers starting with 0 (zero) wouldn’t be valid after removing the first zero. We can’t treat all numbers as “numbers” since there are numbers that should keep their look to remain correct and valid.

The second example demonstrates how will stay a number treated as text and to keep its look. This is a demonstrational query so we wrote the number as text and To_Char function is not needed in this case but you can use this example with other datatype variables in place of ‘01234‘. Take a look at the following query and note that Oracle database left the first zero as is and your phone numbers wouldn’t be ruined using this VARCHAR2 type.

SELECT TO_CHAR('01234') 
  FROM DUAL;

online tech support online computer help computer technician computer problems computer oracle to_char oracle to_char in oracle sql to_char oracle sql to_char in oracle sql database sql retirement planning retirement sql database programming database oracle retirement planning retirement sql database oracle database sql

Also Oracle To_Char can be used to give numbers a certain look so they look better and more clear on a report or a bill. To format a number you would need to use a data mask and for numbers there are limited amount of key-letters. To see number formatting element take a look at the table below or this link will send you directly to Oracle webpage for possible mask elements. The mask key-letters should be used only with Oracle To_Char as on the following example. On this example we are filling in missing zeros after the coma point to have always two places after the decimal point.

SELECT TO_CHAR(111.2,'990D00') AS formatted_number
  FROM DUAL;

online tech support online computer help computer technician computer problems computer oracle to_char oracle to_char in oracle sql to_char oracle sql to_char in oracle sql database sql retirement planning retirement sql database programming database oracle retirement planning retirement sql database oracle database sql

Changing value from number to character is not as popular as turning it from a date type to text and the following part of this tutorial will continue with date values. To change a date look you would need to use a mask again but this time with different mask elements. For example the following Oracle To_Char example has a mask as year(YYYY)-month(MM)-day(DD) hours in 24 digits(HH24):minutes(MI):seconds(SS).

SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS') AS date_to_char
  FROM DUAL;

online tech support online computer help computer technician computer problems computer oracle to_char oracle to_char in oracle sql to_char oracle sql to_char in oracle sql database sql retirement planning retirement sql database programming database oracle retirement planning retirement sql database oracle database sql

Also here you can find a list of possible Oracle masks elements for a date type. Use those mask elements only with To_Char function.


See Also:
Oracle Select Oracle SUBSTR Oracle INSTR Oracle Replace Oracle Length Home

 

Number Format Elements Example Description
, (comma) 9,999 Returns a comma in the specified position. You can specify multiple commas in a number format model.Restrictions:

  • A comma element cannot begin a number format model.
  • A comma cannot appear to the right of a decimal character or period in a number format model.
. (period) 99.99 Returns a decimal point, which is a period (.) in the specified position.Restriction: You can specify only one period in a number format model.
$ $9999 Returns value with a leading dollar sign.
0 09999990 Returns leading zeros.Returns trailing zeros.
9 9999 Returns value with the specified number of digits with a leading space if positive or with a leading minus if negative. Leading zeros are blank, except for a zero value, which returns a zero for the integer part of the fixed-point number.
B B9999 Returns blanks for the integer part of a fixed-point number when the integer part is zero (regardless of zeros in the format model).
C C999 Returns in the specified position the ISO currency symbol (the current value of the NLS_ISO_CURRENCY parameter).
D 99D99 Returns in the specified position the decimal character, which is the current value of the NLS_NUMERIC_CHARACTER parameter. The default is a period (.).Restriction: You can specify only one decimal character in a number format model.
EEEE 9.9EEEE Returns a value using in scientific notation.
G 9G999 Returns in the specified position the group separator (the current value of the NLS_NUMERIC_CHARACTER parameter). You can specify multiple group separators in a number format model.Restriction: A group separator cannot appear to the right of a decimal character or period in a number format model.
L L999 Returns in the specified position the local currency symbol (the current value of the NLS_CURRENCY parameter).
MI 9999MI Returns negative value with a trailing minus sign (-).Returns positive value with a trailing blank.Restriction: The MI format element can appear only in the last position of a number format model.
PR 9999PR Returns negative value in <angle brackets>.Returns positive value with a leading and trailing blank.Restriction: The PR format element can appear only in the last position of a number format model.
RNrn RNrn Returns a value as Roman numerals in uppercase.Returns a value as Roman numerals in lowercase.Value can be an integer between 1 and 3999.
S S99999999S Returns negative value with a leading minus sign (-).Returns positive value with a leading plus sign (+).Returns negative value with a trailing minus sign (-).Returns positive value with a trailing plus sign (+).Restriction: The S format element can appear only in the first or last position of a number format model.
TM TM The text minimum number format model returns (in decimal output) the smallest number of characters possible. This element is case insensitive.The default is TM9, which returns the number in fixed notation unless the output exceeds 64 characters. If the output exceeds 64 characters, then Oracle Database automatically returns the number in scientific notation.Restrictions:

  • You cannot precede this element with any other element.
  • You can follow this element only with one 9 or one E (or e), but not with any combination of these. The following statement returns an error:SELECT TO_CHAR(1234, 'TM9e') FROM DUAL;
U U9999 Returns in the specified position the Euro (or other) dual currency symbol, determined by the current value of the NLS_DUAL_CURRENCY parameter.
V 999V99 Returns a value multiplied by 10n (and if necessary, round it up), where n is the number of 9’s after the V.
X XXXXxxxx Returns the hexadecimal value of the specified number of digits. If the specified number is not an integer, then Oracle Database rounds it to an integer.Restrictions:

  • This element accepts only positive values or 0. Negative values return an error.
  • You can precede this element only with 0 (which returns leading zeroes) or FM. Any other elements return an error. If you specify neither 0 nor FM with X, then the return always has one leading blank.
Datetime Format Element TO_* Datetime functions? Description
-
/
,
.
;
:
"text"
Yes Punctuation and quoted text is reproduced in the result.
AD
A.D.
Yes AD indicator with or without periods.
AM
A.M.
Yes Meridian indicator with or without periods.
BC
B.C.
Yes BC indicator with or without periods.
CC
SCC
Century.

  • If the last 2 digits of a 4-digit year are between 01 and 99 (inclusive), then the century is one greater than the first 2 digits of that year.
  • If the last 2 digits of a 4-digit year are 00, then the century is the same as the first 2 digits of that year.

For example, 2002 returns 21; 2000 returns 20.

D
Yes Day of week (1-7). This element depends on the NLS territory of the session.
DAY
Yes Name of day.
DD
Yes Day of month (1-31).
DDD
Yes Day of year (1-366).
DL
Yes Returns a value in the long date format, which is an extension of the Oracle Database DATE format, determined by the current value of the NLS_DATE_FORMAT parameter. Makes the appearance of the date components (day name, month number, and so forth) depend on the NLS_TERRITORY and NLS_LANGUAGE parameters. For example, in the AMERICAN_AMERICA locale, this is equivalent to specifying the format 'fmDay, Month dd, yyyy'. In the GERMAN_GERMANY locale, it is equivalent to specifying the format ‘fmDay, dd. Month yyyy‘.Restriction: You can specify this format only with the TS element, separated by white space.
DS
Yes Returns a value in the short date format. Makes the appearance of the date components (day name, month number, and so forth) depend on the NLS_TERRITORY and NLS_LANGUAGE parameters. For example, in the AMERICAN_AMERICA locale, this is equivalent to specifying the format ‘MM/DD/RRRR‘. In the ENGLISH_UNITED_KINGDOM locale, it is equivalent to specifying the format ‘DD/MM/RRRR‘.Restriction: You can specify this format only with the TS element, separated by white space.
DY
Yes Abbreviated name of day.
E
Yes Abbreviated era name (Japanese Imperial, ROC Official, and Thai Buddha calendars).
EE
Yes Full era name (Japanese Imperial, ROC Official, and Thai Buddha calendars).
FF [1..9]
Yes Fractional seconds; no radix character is printed. Use the X format element to add the radix character. Use the numbers 1 to 9 after FF to specify the number of digits in the fractional second portion of the datetime value returned. If you do not specify a digit, then Oracle Database uses the precision specified for the datetime data type or the data type’s default precision. Valid in timestamp and interval formats, but not in DATE formats.Examples: 'HH:MI:SS.FF'SELECT TO_CHAR(SYSTIMESTAMP, 'SS.FF3') from DUAL;
FM
Yes Returns a value with no leading or trailing blanks.See Also: Additional discussion on this format model modifier in the Oracle Database SQL Language Reference
FX
Yes Requires exact matching between the character data and the format model.See Also: Additional discussion on this format model modifier in the Oracle Database SQL Language Reference
HH
HH12
Yes Hour of day (1-12).
HH24
Yes Hour of day (0-23).
IW
Week of year (1-52 or 1-53) based on the ISO standard.
IYY
IY
I
Last 3, 2, or 1 digit(s) of ISO year.
IYYY
4-digit year based on the ISO standard.
J
Yes Julian day; the number of days since January 1, 4712 BC. Number specified with J must be integers.
MI
Yes Minute (0-59).
MM
Yes Month (01-12; January = 01).
MON
Yes Abbreviated name of month.
MONTH
Yes Name of month.
PM
P.M.
Yes Meridian indicator with or without periods.
Q
Quarter of year (1, 2, 3, 4; January – March = 1).
RM
Yes Roman numeral month (I-XII; January = I).
RR
Yes Lets you store 20th century dates in the 21st century using only two digits.See Also: “The RR Datetime Format Element”
RRRR
Yes Round year. Accepts either 4-digit or 2-digit input. If 2-digit, provides the same return as RR. If you do not want this functionality, then enter the 4-digit year.
SS
Yes Second (0-59).
SSSSS
Yes Seconds past midnight (0-86399).
TS
Yes Returns a value in the short time format. Makes the appearance of the time components (hour, minutes, and so forth) depend on the NLS_TERRITORY and NLS_LANGUAGE initialization parameters.Restriction: You can specify this format only with the DL or DS element, separated by white space.
TZD 
Yes Daylight saving information. The TZD value is an abbreviated time zone string with daylight saving information. It must correspond with the region specified in TZR. Valid in timestamp and interval formats, but not in DATE formats.Example: PST (for US/Pacific standard time); PDT (for US/Pacific daylight time).
TZH
Yes Time zone hour. (See TZM format element.) Valid in timestamp and interval formats, but not in DATE formats.Example: 'HH:MI:SS.FFTZH:TZM'.
TZM
Yes Time zone minute. (See TZH format element.) Valid in timestamp and interval formats, but not in DATE formats.Example: 'HH:MI:SS.FFTZH:TZM'.
TZR
Yes Time zone region information. The value must be one of the time zone region names supported in the database. Valid in timestamp and interval formats, but not in DATE formats.Example: US/Pacific
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.
X
Yes Local radix character.Example: 'HH:MI:SSXFF'.
Y,YYY
Yes Year with comma in this position.
YEAR
SYEAR
Year, spelled out; S prefixes BC dates with a minus sign (-).
YYYY
SYYYY
Yes 4-digit year; S prefixes BC dates with a minus sign.
YYY
YY
Y
Yes Last 3, 2, or 1 digit(s) of year.
 

This page is based on examples to be easier to follow. Function Oracle Trim will remove or trim characters from your text beginnings and/or ends. The Trim in Oracle will by default remove white spaces from your text and it’s syntax is:

TRIM(<your text>)

The first example is showing how the TRIM is working using the Oracle LENGTH function and you can see below the output after the white spaces are removed the function returns the smaller string amount.

 SELECT ' AAA ' AS original_text, 
         LENGTH (' AAA ') original_text_length, 
         trim (' AAA ') AS trimmed_text, 
         LENGTH (trim (' AAA ')) trimmed_text_length
   FROM dual;

online tech support computer help computer technician computer problems computer oracle trim oracle trim in oracle sql database oracle sql database programming database oracle retirement planning retirement sql database oracle database sql

Also Oracle trim lets you to remove other characters and to do it there are 3 options. The first option is to remove the characters from the beginning of the text. The TRIM function can be used with an extra keyword named LEADING and that makes to trim only from the beginning part of a string. The syntax is following:

TRIM(LEADING <char to be removed>  FROM <your text>)

The example removes #-characters from the beginning and it leaves the # character ending part of the text as is.

 SELECT '#AAA#' AS original_text,
        trim (LEADING '#' FROM '#AAA#') AS trim_leading
   FROM dual;

online tech support computer help computer technician computer problems computer oracle trim oracle trim in oracle sql database oracle sql database programming database oracle retirement planning retirement sql database oracle database sql

To remove the selected characters from the end you can use the TRAILING keyword. The syntax with the TRIM with TRAILING is:

TRIM(TRAILING <char to be removed>  FROM <your text>)

The following example is using the TRAILING keyword in Oracle TRIM and it does remove the #-character from the end of the text but it leaves the beginning part untouched.

 SELECT '#AAA#' AS original_text,
        trim (TRAILING '#' FROM '#AAA#') AS trim_trailing
   FROM dual;

online tech support computer help computer technician computer problems computer oracle trim oracle trim in oracle sql database oracle sql database programming database oracle retirement planning retirement sql database oracle database sql

To remove the defined character from the end and beginning use in Oracle TRIM the BOTH keyword or the second option is without any keyword at all. The both options working in the same way. The TRIM syntax with BOTH is:

TRIM([BOTH] <char to be removed>  FROM <your text>)

The last example cleans the text from the #-characters and it contains both options for trimming.

 SELECT '#AAA#' AS original_text,
        trim (BOTH '#' FROM '#AAA#') AS trim_both,
        trim ('#' FROM '#AAA#') AS trim
   FROM dual;

online tech support computer help computer technician computer problems computer oracle trim oracle trim in oracle sql database oracle sql database programming database oracle retirement planning retirement sql database oracle database sql

It is important to know that Oracle Trim is removing characters only from the beginning and end and it does leave the characters in the middle of string. If you need to remove all define characters use the Oracle Replace function instead.



See Also:
Online Tech Support Home Oracle Select Oracle Replace

 

This tutorial is based on examples to be easier to follow. The Oracle Create View Statement allows creating a view object in Oracle database. The Oracle View is a logical object that doesn’t store any data in itself and it uses other objects or tables to link or to get its data. Tables used in view are called base tables. The Oracle Create View syntax is following:

CREATE [OR REPLACE] VIEW <view_name>
AS
<select_statement>;

To demonstrate a view creation and its context we would need a table or a query. To keep the example as simple as possible we will use the following Select statement using the Oracle Dual table that does exist in all Oracle database versions. Please take a look at the output to understand the following examples better.

 SELECT DECODE (MOD (rownum,4),0,dummy,rownum) AS ID
   FROM dual
    CONNECT BY rownum < 11;

online tech support computer help computer technician computer problems computer oracle create view oracle create view in oracle drop view oracle drop view in oracle sql database oracle sql database programming database oracle retirement planning retirement sql database oracle database sql

From it output you can find the ID column with mixed values number from 1 to 10 and some “X” characters in the middle. The next script will create an Oracle view name MY_DUAL_VW and as you see after the AS keyword the Select statement is exactly the same as above.

CREATE VIEW MY_DUAL_VW
AS
SELECT DECODE (MOD (rownum,4),0,dummy,rownum) AS ID
   FROM dual
    CONNECT BY rownum < 11;

online tech support computer help computer technician computer problems computer oracle create view oracle create view in oracle drop view oracle drop view in oracle sql database oracle sql database programming database oracle retirement planning retirement sql database oracle database sql

The all Oracle Views can be used as Oracle tables except that they don’t store the data in itself, so you can use the view name in the SQL query. The following Select statement is using the just created view and since the Oracle view is using the first query above the output looks exactly the same as the first query has.

SELECT *
   FROM MY_DUAL_VW;

online tech support computer help computer technician computer problems computer oracle create view oracle create view in oracle drop view oracle drop view in oracle sql database oracle sql database programming database oracle retirement planning retirement sql database oracle database sql

To see an Oracle View columns and their attributes you can use command DESCRIBE. The view used in this examples doesn’t have many column so it is very simple to read it but for bigger views the DESCRIBE command comes quite handy to find out column names or the column data types.

describe MY_DUAL_VW;

online tech support computer help computer technician computer problems computer oracle create view oracle create view in oracle drop view oracle drop view in oracle sql database oracle sql database programming database oracle retirement planning retirement sql database oracle database sql

To remove an Oracle View from a database use command DROP. The command removes only the view itself and leaves the other objects used in the view as is. Also the DROP command does not delete lines in the Oracle tables used in the view and it only applies on the view object.

DROP <view_name>;

The following Online Tech Support example will remove the MY_DUAL_VW view that was created above.

DROP VIEW MY_DUAL_VW;

online tech support computer help computer technician computer problems computer oracle create view oracle create view in oracle drop view oracle drop view in oracle sql database oracle sql database programming database oracle retirement planning retirement sql database oracle database sql

We will create the MY_DUAL_VW view again but this time all “X” will be removed from the list. This example show one of the reasons why Oracle View are present in Oracle databases and the reason is you can modify the output without deleting any line in the Oracle table. Also you don’t have to copy the lines over to another table and that way your database size remains smaller and less programming will be needed to synchronize both tables.

CREATE VIEW MY_DUAL_VW AS
 SELECT t.ID
   FROM
    (SELECT DECODE (MOD (rownum,4),0,dummy,rownum) AS ID
       FROM dual
        CONNECT BY rownum < 11 ) t
  WHERE t.ID <> 'X';

online tech support computer help computer technician computer problems computer oracle create view oracle create view in oracle drop view oracle drop view in oracle sql database oracle sql database programming database oracle retirement planning retirement sql database oracle database sql

And the following SQL query shows that all “X” values are missing from the new MY_DUAL_VW view.

SELECT *
   FROM MY_DUAL_VW;

online tech support computer help computer technician computer problems computer oracle create view oracle create view in oracle drop view oracle drop view in oracle sql database oracle sql database programming database oracle retirement planning retirement sql database oracle database sql

The Oracle Views allowing us to filter out lines that are not needed or relevant without removing anything from the base tables and once the logic has changed you do have to change only the Oracle View to apply new rules.



See Also:
Oracle Select Home

 

This online tutorial page is based on examples to be easier to follow. The Oracle To_Date function converts data types like string of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 to a DATE data type and this way you will be able to do date operations. You can leave the date format away if your date value in string has written in default database date format. Also NLS_DATE_FORMAT is optional but it gives you an opportunity to export the date value in another time region look. The Oracle To_Date syntax is following:

TO_DATE (<string>[,<date_format>] [,NLS_DATE_FORMAT]);

The first example has date November 20th 2010 in string as “2010/11/20” and behind it we have declared the Oracle date format that describes the date value look. Online Tech Support recommends to use always the date format even it is optional. Using a date format your date value will be converted always correctly and the default database date format can be changed without affecting any code.

 SELECT TO_DATE ('2010/11/20','yyyy/mm/dd') AS my_date
   FROM dual;

online tech support computer help computer technician computer problems computer oracle to_date oracle to_date in oracle sql to_date oracle sql to_date in oracle sql database oracle database sql oracle date format oracle date format in oracle sql date format

The second example with Oracle To_Date is using the same date value as above only with different format. The example above had number of years then months and in the last days but the following example has first number months then days and to last comes years. Also take a look at the date format it has changed among of the date value and reflects the date structure.

 SELECT to_date ('11/20/2010','mm/dd/yyyy') AS my_date
   FROM dual;

online tech support computer help computer technician computer problems computer oracle to_date oracle to_date in oracle sql to_date oracle sql to_date in oracle sql database oracle database sql oracle date format oracle date format in oracle sql date format

We have been using “/“-characters to separate date numbers on the last two examples above but using them is not obligated and the next example shows that the date value can contain only numbers. The value will be converted correctly while the date format is declared to reflect the value. Take a look at the output below the date has still remained to November 20th 2010.

 SELECT to_date ('20112010','ddmmyyyy') AS my_date
   FROM dual;

online tech support computer help computer technician computer problems computer oracle to_date oracle to_date in oracle sql to_date oracle sql to_date in oracle sql database oracle database sql oracle date format oracle date format in oracle sql date format

The Oracle To_Date function is so flexible that you can leave away even some date values. The fourth example has only month and year numbers declared and once again the date format allows the To_Date function to do conversion correctly. Since the day number is missing the date will become first day of month and the output has November 1st 2010.

 SELECT to_date ('11/2010','mm/yyyy') AS my_date
   FROM dual;

online tech support computer help computer technician computer problems computer oracle to_date oracle to_date in oracle sql to_date oracle sql to_date in oracle sql database oracle database sql oracle date format oracle date format in oracle sql date format

To convert a string to date type using only a year number is described on the following example above. The last example had no day number and the Oracle database took the first number of declared month but leaving away the month number you will receive the current month and not the first month of year as you may guessed. The example above has two columns where the first is converted date using year 2010 number and the second column has current date the April 15th 2011. Now have a look at the converted column “MY_DATE” the date is April 1st 2010.

 SELECT to_date ('2010','yyyy') AS my_date, SYSDATE
   FROM dual;

online tech support computer help computer technician computer problems computer oracle to_date oracle to_date in oracle sql to_date oracle sql to_date in oracle sql database oracle database sql oracle date format oracle date format in oracle sql date format



See Also:
Oracle Select Oracle Date Format Oracle Date Difference Home

Oracle Date and Time Format Elements

Element TO_* datetime functions? Description
AD
A.D.
Yes AD indicator with or without periods.
AM
A.M.
Yes Meridian indicator with or without periods.
BC
B.C.
Yes BC indicator with or without periods.
CC
SCC
Century.

  • If the last 2 digits of a 4-digit year are between 01 and 99 (inclusive), then the century is one greater than the first 2 digits of that year.
  • If the last 2 digits of a 4-digit year are 00, then the century is the same as the first 2 digits of that year.

For example, 2002 returns 21; 2000 returns 20.

D
Yes Day of week (1-7). This element depends on the NLS territory of the session.
DAY
Yes Name of day.
DD
Yes Day of month (1-31).
DDD
Yes Day of year (1-366).
DL
Yes Returns a value in the long date format, which is an extension of the Oracle Database DATE format, determined by the current value of the NLS_DATE_FORMAT parameter. Makes the appearance of the date components (day name, month number, and so forth) depend on the NLS_TERRITORY and NLS_LANGUAGE parameters. For example, in the AMERICAN_AMERICA locale, this is equivalent to specifying the format 'fmDay, Month dd, yyyy'. In the GERMAN_GERMANY locale, it is equivalent to specifying the format ‘fmDay, dd. Month yyyy‘.Restriction: You can specify this format only with the TS element, separated by white space.
DS
Yes Returns a value in the short date format. Makes the appearance of the date components (day name, month number, and so forth) depend on the NLS_TERRITORY and NLS_LANGUAGE parameters. For example, in the AMERICAN_AMERICA locale, this is equivalent to specifying the format ‘MM/DD/RRRR‘. In the ENGLISH_UNITED_KINGDOM locale, it is equivalent to specifying the format ‘DD/MM/RRRR‘.Restriction: You can specify this format only with the TS element, separated by white space.
DY
Yes Abbreviated name of day.
E
Yes Abbreviated era name (Japanese Imperial, ROC Official, and Thai Buddha calendars).
EE
Yes Full era name (Japanese Imperial, ROC Official, and Thai Buddha calendars).
FF [1..9]
Yes Fractional seconds; no radix character is printed. Use the X format element to add the radix character. Use the numbers 1 to 9 after FF to specify the number of digits in the fractional second portion of the datetime value returned. If you do not specify a digit, then Oracle Database uses the precision specified for the datetime data type or the data type’s default precision. Valid in timestamp and interval formats, but not in DATE formats.Examples: 'HH:MI:SS.FF'SELECT TO_CHAR(SYSTIMESTAMP, 'SS.FF3') from DUAL;
FM
Yes Returns a value with no leading or trailing blanks.See Also: Additional discussion on this format model modifier in the Oracle Database SQL Language Reference
FX
Yes Requires exact matching between the character data and the format model.See Also: Additional discussion on this format model modifier in the Oracle Database SQL Language Reference
HH
HH12
Yes Hour of day (1-12).
HH24
Yes Hour of day (0-23).
IW
Week of year (1-52 or 1-53) based on the ISO standard.
IYY
IY
I
Last 3, 2, or 1 digit(s) of ISO year.
IYYY
4-digit year based on the ISO standard.
J
Yes Julian day; the number of days since January 1, 4712 BC. Number specified with J must be integers.
MI
Yes Minute (0-59).
MM
Yes Month (01-12; January = 01).
MON
Yes Abbreviated name of month.
MONTH
Yes Name of month.
PM
P.M.
Yes Meridian indicator with or without periods.
Q
Quarter of year (1, 2, 3, 4; January – March = 1).
RM
Yes Roman numeral month (I-XII; January = I).
RR
Yes Lets you store 20th century dates in the 21st century using only two digits.See Also: “The RR Datetime Format Element”
RRRR
Yes Round year. Accepts either 4-digit or 2-digit input. If 2-digit, provides the same return as RR. If you do not want this functionality, then enter the 4-digit year.
SS
Yes Second (0-59).
SSSSS
Yes Seconds past midnight (0-86399).
TS
Yes Returns a value in the short time format. Makes the appearance of the time components (hour, minutes, and so forth) depend on the NLS_TERRITORY and NLS_LANGUAGE initialization parameters.Restriction: You can specify this format only with the DL or DS element, separated by white space.
TZD
Yes Daylight saving information. The TZD value is an abbreviated time zone string with daylight saving information. It must correspond with the region specified in TZR. Valid in timestamp and interval formats, but not in DATE formats.Example: PST (for US/Pacific standard time); PDT (for US/Pacific daylight time).
TZH
Yes Time zone hour. (See TZM format element.) Valid in timestamp and interval formats, but not in DATE formats.Example: 'HH:MI:SS.FFTZH:TZM'.
TZM
Yes Time zone minute. (See TZH format element.) Valid in timestamp and interval formats, but not in DATE formats.Example: 'HH:MI:SS.FFTZH:TZM'.
TZR
Yes Time zone region information. The value must be one of the time zone region names supported in the database. Valid in timestamp and interval formats, but not in DATE formats.Example: US/Pacific
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.
X
Yes Local radix character.Example: 'HH:MI:SSXFF'.
Y,YYY
Yes Year with comma in this position.
YEAR
SYEAR
Year, spelled out; S prefixes BC dates with a minus sign (-).
YYYY
SYYYY
Yes 4-digit year; S prefixes BC dates with a minus sign.
YYY
YY
Y
Yes Last 3, 2, or 1 digit(s) of year.
 

This learning page is based on examples. Oracle LPAD function allows formatting an output of a SQL query or a PL/SQL code. The function fills the string from left side with defined characters and by default the padding character is “space”. The Oracle LPAD syntax is following:

LPAD (<string>, <nr_of_length> [,<padding_string>]);

The following LPAD examples are using text “Oracle DBA” and to know the string length we will use Oracle Length function. Take a look at the following example below.

 SELECT  length('Oracle DBA')
   FROM dual;

online tech support computer help computer technician computer problems computer oracle lpad in oracle sql lpad oracle sql lpad in oracle sql database oracle database sql length oracle length in oracle sql length oracle sql database software database retirement planning retirement

The output of the last query shows 10 characters. To pad the character we will set Oracle LPAD length to 11 and the output shows the “Oracle DBA” string (with a space in front of it). The LPAD fills by default the free space with white-space.

 SELECT LPAD ('Oracle DBA',11)
   FROM dual;

online tech support computer help computer technician computer problems computer oracle lpad in oracle sql lpad oracle sql lpad in oracle sql database oracle database sql length oracle length in oracle sql length oracle sql database software database retirement planning retirement

The second Oracle LPAD example is same as the last one only in this time we will define our own character and this will be an asterisk (*). You will see a *-character in front of the “Oracle DBA” string in the output. You may wonder why there is only 1 star – we did declare the length to 11 characters. The Oracle LPAD fills only the free places from left.

 SELECT LPAD ('Oracle DBA',11,'*')
   FROM dual;

online tech support computer help computer technician computer problems computer oracle lpad in oracle sql lpad oracle sql lpad in oracle sql database oracle database sql length oracle length in oracle sql length oracle sql database software database retirement planning retirement

The third LPAD query has length less than 10 characters which is the input string length. Take a look at the query output the “Oracle DBA” has become to “Oracle” and there are no star characters on the left side. Oracle LPAD will truncate your input string if it doesn’t fit into the length size and there are no stars because no free space has available. The input string doesn’t fit into 6 characters.

 SELECT LPAD ('Oracle DBA',6,'*')
   FROM dual;

online tech support computer help computer technician computer problems computer oracle lpad in oracle sql lpad oracle sql lpad in oracle sql database oracle database sql length oracle length in oracle sql length oracle sql database software database retirement planning retirement

The example below shows how to format the SQL query output using Oracle LPAD function. The Select statement is done using Oracle Dual table and the CONNECT BY hierarchical operator to generate 10 lines. The Oracle Rownum function gets its value dynamically increasing per every next row and it set in the query to use it as the LPAD length parameter. For example on the first row’s LPAD length will be 10 + 1 or 11 and on the 10th row’s length are 20 characters. Take a look at the output how the left-padded free space will be filled with *-characters.

 SELECT LPAD ('Oracle DBA',10+rownum,'*')
   FROM dual
    CONNECT BY rownum < 11;

online tech support computer help computer technician computer problems computer oracle lpad in oracle sql lpad oracle sql lpad in oracle sql database oracle database sql length oracle length in oracle sql length oracle sql database software database retirement planning retirement

It’s important to know that pad string doesn’t have to be only 1 character it can be a text or a pattern. The query below is same as the last one with only larger LPAD string “*-*-” and Oracle LPAD will use for first 3 lines only part of it. The pad string will be in its full length starting from the 4th row.

 SELECT LPAD ('Oracle DBA',10+rownum,'*-*-')
   FROM dual
    CONNECT BY rownum < 11;

online tech support computer help computer technician computer problems computer oracle lpad in oracle sql lpad oracle sql lpad in oracle sql database oracle database sql length oracle length in oracle sql length oracle sql database software database retirement planning retirement

The Oracle LPAD truncating example below has Oracle Rownum amount subtracted from the amount of 10 (10-rownum) and since we do have 10 rows the lowest row LPAD length will become zero. The SQL query returns text with decreasing length amount and that is smaller than the input string own length (10).

 SELECT LPAD ('Oracle DBA',10-rownum,'*')
   FROM dual
    CONNECT BY rownum < 11;

online tech support computer help computer technician computer problems computer oracle lpad in oracle sql lpad oracle sql lpad in oracle sql database oracle database sql length oracle length in oracle sql length oracle sql database software database retirement planning retirement

Oracle LPAD can become quite handy to find out white-spaces in the string on the left side or it can be used on reports to align text on left. There are many more ways to format the output using Oracle LPAD and that makes it quite important to know.



See Also:
Oracle Select Home

 

This tutorial is based on examples to be easier to follow. The Oracle Cast function allows converting one built-in type of value to another one. In other-words using Oracle Cast you can change a value to a different value type. Also Cast can be used to insert a set of data into a collection type. The Oracle Cast syntax for converting a value to another type is following:

CAST (<value> AS <convert_into_type_name>);

The first Oracle Cast example will convert a date value to a Timestamp. You are able to see how the time digits became longer in the output and since a Timestamp is more accurate than a date value, it stores smaller units than a date. Of course the conversion is missing the time units and they are all set to zero.

 SELECT CAST (TO_DATE ('11/04/2013','DD/MM/YYYY')
              AS TIMESTAMP WITH LOCAL TIME ZONE) AS my_timestamp
   FROM DUAL;

online tech support computer help computer technician computer problems computer cast oracle cast in oracle sql cast oracle sql database oracle database sql database programming database oracle retirement planning retirement sql database oracle database sql

This example below is an opposite way conversion than the first example because now we will convert a Timestamp value to a date. Take a look at the SQL query output to see that time zone attributes are missing since a date don’t have them.

 SELECT CAST(systimestamp AS DATE) AS THE_DATE
   FROM DUAL;

online tech support computer help computer technician computer problems computer cast oracle cast in oracle sql cast oracle sql database oracle database sql database programming database oracle retirement planning retirement sql database oracle database sql

To cast a number to a text keep in mind that a number doesn’t keep zero values in front and you can’t save them even with Oracle Cast function. This is one of the reasons why mobile numbers and ID numbers are stored as text to keep them with their original look.

 SELECT CAST(0000111001 AS VARCHAR2(30)) AS my_text
   FROM DUAL;

online tech support computer help computer technician computer problems computer cast oracle cast in oracle sql cast oracle sql database oracle database sql database programming database oracle retirement planning retirement sql database oracle database sql

The fourth example shows how to convert a Rowid value into text. The Rowid values are quite useful to find out the certain row in a table. They are widely used in debugging process to point to a certain row that caused a trouble.

 SELECT CAST(rowid AS VARCHAR2(30)) AS my_text
   FROM DUAL;

online tech support computer help computer technician computer problems computer cast oracle cast in oracle sql cast oracle sql database oracle database sql database programming database oracle retirement planning retirement sql database oracle database sql

Oracle Cast can be used with the Oracle collection types and the advantage is that you can insert them into a type inside SQL query and that way you code will be smaller and cleaner. The Oracle Cast syntax for casting a data set into a collection type is following:

CAST (MULTISET (<select_statement>) AS <convert_to_a_collection_type_name>);

To try the MULTISET keyword in Oracle Cast function we need a collection type. The following script will create a type named ROWNUMS_T as a table of numbers.

CREATE TYPE rownums_t AS TABLE OF NUMBER;
/

online tech support computer help computer technician computer problems computer cast oracle cast in oracle sql cast oracle sql database oracle database sql database programming database oracle retirement planning retirement sql database oracle database sql

Now we need a Select query that returns only numbers. To keep this example as simple as possible we are going to use the Oracle Dual table and hierarchical operator CONNECT BY to create 10 lines with number from 1 to 10. Take a look at the following SQL query.

 SELECT rownum
     FROM DUAL
  CONNECT BY rownum < 11;

online tech support computer help computer technician computer problems computer cast oracle cast in oracle sql cast oracle sql database oracle database sql database programming database oracle retirement planning retirement sql database oracle database sql

We are ready to try out the example since we did create the ROWNUMS_T type and we have a query that returns only number (take a look at the SQL query above). We will use both objects in the Oracle Cast function in the example above. The Oracle SQL Developer shows the context of the ROWNUMS_T type and they are numbers from 1 to 10.

 SELECT du1.dummy,
    CAST (MULTISET (SELECT rownum
                      FROM DUAL
                   CONNECT BY rownum < 11)
                     AS rownums_t) as casted_rownums
   FROM dual du1;

online tech support computer help computer technician computer problems computer cast oracle cast in oracle sql cast oracle sql database oracle database sql database programming database oracle retirement planning retirement sql database oracle database sql

Take a look at the table below to see values that can be used with Oracle Cast function.


See Also:
Home Oracle Select

Casting Built-In Data Types:

from BINARY_FLOAT, BINARY_DOUBLE from CHAR, VARCHAR2 from NUMBER from DATETIME / INTERVAL (Note 1) from RAW from ROWID, UROWID (Note 2) from NCHAR, NVARCHAR2
to BINARY_FLOAT, BINARY_DOUBLE Y Y Y Y
to CHAR, VARCHAR2 Y Y Y Y Y Y
to NUMBER X Y Y Y
to DATE, TIMESTAMP, INTERVAL Y Y
to RAW Y Y
to ROWID, UROWID Y Y
to NCHAR, NVARCHAR2 Y Y Y Y Y Y

Note 1: Datetime/interval includes DATE, TIMESTAMP, TIMESTAMP WITH TIMEZONE, INTERVAL DAY TO SECOND, and INTERVAL YEAR TO MONTH.

Note 2: You cannot cast a UROWID to a ROWID if the UROWID contains the value of a ROWID of an index-organized table.

 

This tutorial page is based on examples. The Oracle RPAD function allows to format a SQL query or PL/SQL code output. The function fills the output from right side with defined characters and by default the padding character is “space”. Function Oracle Rpad syntax is following:

RPAD (<string>, <nr_of_length> [,<padding_string>]);

The following Rpad examples are going to use input string “Oracle DBA” and to understand the examples better we need to know the length of string. Online Tech Support will use Oracle Length function to get the length number. Take a look at the following Oracle Length example.

 SELECT length ('Oracle DBA')
   FROM dual;

online tech support online computer help computer technician computer problems computer oracle rpad oracle rpad in oracle sql rpad oracle sql rpad in oracle sql database oracle database sql length oracle length in oracle sql length oracle sql

The output of the last SQL query has returned number 10 and that means the string is long 10 characters. To pad the character we will set Oracle Rpad length to 11 and the output shows the “Oracle DBA” string having an extra space in the end. Oracle Rpad fills by default the length with “white-spaces”.

 SELECT rpad ('Oracle DBA',11)
   FROM dual;

online tech support online computer help computer technician computer problems computer oracle rpad oracle rpad in oracle sql rpad oracle sql rpad in oracle sql database oracle database sql length oracle length in oracle sql length oracle sql

The next Oracle Rpad example is almost the same as the last one with only this time we will define our own character and this will be a star (*). It was quite difficult to see the white-space in the end on the last SQL query output, so this time the Rpad function will reveal the padding more clear. You will see in the output a *-character in the end of the “Oracle DBA” text. The next question may raise – why is there only 1 star? We did declare the length to 11 character. The Oracle Rpad fills only the extra places on right side after the string with the Rpad character(s).

 SELECT rpad ('Oracle DBA',11,'*')
   FROM dual;

online tech support online computer help computer technician computer problems computer oracle rpad oracle rpad in oracle sql rpad oracle sql rpad in oracle sql database oracle database sql length oracle length in oracle sql length oracle sql

The third Rpad query has set length to 6 character that is less than the length of the input string has. Take a look at the query output below the “Oracle DBA” has become to “Oracle” and there is no “star” character on the right side. Oracle Rpad will truncate your input string if it doesn’t fix into the length size and there is no stars because no extra space has available. The input string doesn’t fit into 6 character.

 SELECT rpad ('Oracle DBA',6,'*')
   FROM dual;

online tech support online computer help computer technician computer problems computer oracle rpad oracle rpad in oracle sql rpad oracle sql rpad in oracle sql database oracle database sql length oracle length in oracle sql length oracle sql

The following example below is one of the ways to format the SQL query output using Oracle Rpad with Rownum. The Select statement is done using Oracle Dual table and the CONNECT BY hierarchical operator that generates 10 lines. The Oracle Rownum function gets its value dynamically and it is increasing per every next row by 1 number. Also the query is using the Oracle Rownum function in it’s Rpad length. For example the first row’s Rpad length will be 10 + 1 or 11 and the 10th row’s length is 20 characters. Take a look at the output how the right-padded extra size will be filled with *-characters.

 SELECT rpad ('Oracle DBA',10+rownum,'*')
   FROM dual
    CONNECT BY rownum < 11;

online tech support online computer help computer technician computer problems computer oracle rpad oracle rpad in oracle sql rpad oracle sql rpad in oracle sql database oracle database sql length oracle length in oracle sql length oracle sql

It is quite important to know that the pad string doesn’t have to be only 1 character it can be a text or a pattern. The next SQL query below is same as the last one with only larger Rpad string “*-*-” and function Rpad will show on the first three lines only part of the pad string. The pad string will be in it’s full length starting from the fourth row.

 SELECT rpad ('Oracle DBA',10+rownum,'*-*-')
   FROM dual
    CONNECT BY rownum < 11;

online tech support online computer help computer technician computer problems computer oracle rpad oracle rpad in oracle sql rpad oracle sql rpad in oracle sql database oracle database sql length oracle length in oracle sql length oracle sql

The Oracle Rpad truncating example below has Oracle Rownum amount subtracted from the initial amount 10 (10-rownum) and since we do have 10 rows the lowest Rpad row length will become to zero. Take a look what the SQL query returns with decreasing length amount that is smaller than the input string length 10.

 SELECT rpad ('Oracle DBA',10-rownum,'*')
   FROM dual
    CONNECT BY rownum < 11;

online tech support online computer help computer technician computer problems computer oracle rpad oracle rpad in oracle sql rpad oracle sql rpad in oracle sql database oracle database sql length oracle length in oracle sql length oracle sql

Oracle Rpad can become quite handy to find out white-spaces in the string left side or it can be used on reports setting characters to align on right. There are many more ways to format the output using Oracle Rpad and that makes it important to know.



See Also:
Oracle Select Home

 

This tutorial is based on examples to be easier to follow. The Oracle Round function allows rounding a number value to “x” places up after or before the decimal point. The Oracle Round syntax is following:

ROUND (<number> [,<rounding_integer>]);

This Oracle Round example has input value 12.3333333 and it will round the number up to a decimal place and it returns an integer value (number 12). This way rounding is done when the “rounding integer” has left out and by default the Round function removes all numbers after decimal place.

 SELECT ROUND (12.3333333) AS rounded
   FROM dual;

online tech support online computer help computer technician computer problems computer oracle round oracle round in oracle sql round oracle sql round in oracle sql database sql database programming database oracle retirement planning retirement sql database oracle database sql

To leave some numbers after the decimal place use a positive number in the “rounding integer” place. The following example has the same input number as on the last example and the rounding integer is set 2. The output shows the 12.33 value that is exactly two numbers after the decimal place.

 SELECT ROUND (12.3333333,2) AS rounded
   FROM dual;

online tech support online computer help computer technician computer problems computer oracle round oracle round in oracle sql round oracle sql round in oracle sql database programming database oracle retirement planning retirement sql database oracle database sql

To round up before the decimal place use a negative number in the “rounding integer” place. This example we will round only 1 place up of value 12.3333333 and the output value has become number 10. If the input values would have been 16 then the output would have number 20 now.

 SELECT ROUND (12.3333333,-1) AS rounded
   FROM dual;

online tech support online computer help computer technician computer problems computer oracle round oracle round in oracle sql round oracle sql round in oracle sql database programming database oracle retirement planning retirement sql database oracle database sql

The following example has almost the same query as the last one – only this time we will round up 2 places before decimal place. The “rounding integer” has set “-2” and the number 12.3333333 has become 0.

 SELECT ROUND (12.3333333,-2) AS rounded
   FROM dual;

online tech support online computer help computer technician computer problems computer oracle round oracle round in oracle sql round oracle sql round in oracle sql database programming database oracle retirement planning retirement sql database oracle database sql

The SQL query below does rounding with a dynamic value and it comes from pseudo-column named “Rownum“. Oracle ROWNUM value grows with every next line and that since it has been used as “rounding integer” than leaves more decimal places to available with every next line until it becomes a full value.

 SELECT ROUND (12.3333333,rownum) AS rounded
   FROM dual
    CONNECT BY rownum < 8;

online tech support online computer help computer technician computer problems computer oracle round oracle round in oracle sql round oracle sql round in oracle sql database programming database oracle retirement planning retirement sql database oracle database sql

The output shows how the rounding leaves more number available after the decimal place with growing Oracle ROWNUM value.



See Also:
Home Oracle Select Oracle To_Number Oracle Trunc

 

This tutorial is based on examples to be easier to follow. The Oracle Ceil function allows rounding a number value up and the function removes all numbers after the decimal place. The Oracle Ceil syntax is following:

CEIL (<number>);

The first Oracle Ceil example has input value 11.11111 and it rounds the number up and returns an integer value (number 12). The Ceil function always removes all numbers after a decimal place.

 SELECT CEIL (11.11111)
   FROM dual;

online tech support online computer help computer technician computer problems computer oracle round oracle round in oracle sql round oracle sql round in oracle sql database sql oracle ceil oracle ceil in oracle sql ceil oracle sql ceil in oracle sql database programming database oracle retirement planning retirement sql database oracle database sql

When the numbers after the decimal place will remain all zeros then Oracle Ceil returns equal integer value and on the following example it is number 11. Have a look at the example below.

 SELECT CEIL (11.00)
   FROM dual;

online tech support online computer help computer technician computer problems computer oracle round oracle round in oracle sql round oracle sql round in oracle sql database sql oracle ceil oracle ceil in oracle sql ceil oracle sql ceil in oracle sql database programming database oracle retirement planning retirement sql database oracle database sql

The Ceil function will round up even the smallest fraction of the number and in this case the input number is 11.00000001. No any other rounding function beside Ceil will return number 12 with it.

 SELECT CEIL (11.00000001)
   FROM dual;

online tech support online computer help computer technician computer problems computer oracle round oracle round in oracle sql round oracle sql round in oracle sql database sql oracle ceil oracle ceil in oracle sql ceil oracle sql ceil in oracle sql database programming database oracle retirement planning retirement sql database oracle database sql

The following example will be round to 12 even with the Oracle Round function and the example demonstrates that there is no exceptions in Oracle Ceil.

 SELECT CEIL (11.9999999)
   FROM dual;

online tech support online computer help computer technician computer problems computer oracle round oracle round in oracle sql round oracle sql round in oracle sql database sql oracle ceil oracle ceil in oracle sql ceil oracle sql ceil in oracle sql database programming database oracle retirement planning retirement sql database oracle database sql

The next example is about how to round up using Oracle Ceil with keeping the decimal places in number. First we do know Ceil returns only an integer value, so we need to move decimal place to lower position before applying function Ceil. The following example we would like to keep 3 numbers after a decimal place and we will multiply the input number 11.11111 with 1000.

 SELECT 11.11111 * 1000
   FROM dual;

online tech support online computer help computer technician computer problems computer oracle round oracle round in oracle sql round oracle sql round in oracle sql database sql oracle ceil oracle ceil in oracle sql ceil oracle sql ceil in oracle sql database programming database oracle retirement planning retirement sql database oracle database sql

Now it is time to apply Oracle Ceil on 11111.11.

 SELECT CEIL (11111.11)
   FROM dual;

online tech support online computer help computer technician computer problems computer oracle round oracle round in oracle sql round oracle sql round in oracle sql database sql oracle ceil oracle ceil in oracle sql ceil oracle sql ceil in oracle sql database programming database oracle retirement planning retirement sql database oracle database sql

To get the numbers after decimal place back we need to divide with 1000 the last integer (11112).

 SELECT 11112 / 1000
   FROM dual;

online tech support online computer help computer technician computer problems computer oracle round oracle round in oracle sql round oracle sql round in oracle sql database sql oracle ceil oracle ceil in oracle sql ceil oracle sql ceil in oracle sql database programming database oracle retirement planning retirement sql database oracle database sql

The example above is a workaround to do round up and to keep the decimal places. The output on the last example cannot be achieved using Oracle Round because as it does not round up. Take a look at the following example.

 SELECT ROUND(11.11111,3)
  FROM dual;

online tech support online computer help computer technician computer problems computer oracle round oracle round in oracle sql round oracle sql round in oracle sql database sql oracle ceil oracle ceil in oracle sql ceil oracle sql ceil in oracle sql database programming database oracle retirement planning retirement sql database oracle database sql

As you see on the output it didn’t become 11.112 instead it is 11.111.

To round up or down a number take a look at the links below. To round down and to receive an integer value use Oracle Floor or Oracle Trunc and to normal rounding use Oracle Round function.



See Also:
Home Oracle Select Oracle To_Number Oracle Trunc Oracle Round