SERPland Flugverkehr/Airtraffic Zürich – Auto Direktimport Dänemark – Disneyland Paris – Legoland Deutschland

17Oct/110

TO_CHAR Oracle Function

With the function TO_CHAR, Oracle converts a number or date to a string.

The to_char Oracle syntax is:

to_char( value, [ format_mask ], [ nls_language ] )

  • value can either be a number or date that will be converted to a string.
  • format_mask is optional. This is the format that will be used to convert value to a string.
  • nls_language is optional. This is the nls language used to convert value to a string.

So, to convert a date variable to a text (string), you would use the to_char Oracle function.

TO_CHAR Oracle within SQL

select to_char(sysdate, 'yyyy/mm/dd') as to_char_oracle from dual; /* to_char Oracle SQL returns: '2011/07/17' */

select to_char(sysdate, 'Month DD, YYYY') as to_char_oracle from dual; /* to_char Oracle SQL returns: 'October 17, 2011' */

select to_char(sysdate, 'FMMonth DD, YYYY') as to_char_oracle from dual; /* to_char Oracle SQL returns: 'October 17, 2011' */

select to_char(sysdate, 'FMMON DDth, YYYY') as to_char_oracle from dual; /* to_char Oracle SQL returns: 'OCT 17TH, 2011' */

select to_char(sysdate, 'FMMon ddth, YYYY') as to_char_oracle from dual; /* to_char Oracle SQL returns: 'Oct 17th, 2011' */

TO_CHAR Oracle within PL/SQL

(Please turn serveroutput on)

SET serveroutput ON

Here, within PL/SQL loop with the function to_char oracle returns the number i converted to a text string:

BEGIN

FOR i IN REVERSE 1 .. 3 LOOP

DBMS_OUTPUT.put_line(TO_CHAR(i) ||' - converted by to_char Oracle function');

END LOOP;

END;

Result:

3 - converted by to_char Oracle function

2 - converted by to_char Oracle function

1 - converted by to_char Oracle function

 

Related Posts:

4Jun/070

pl/sql: exit an enclosing loop (leaving a labeled loop)


You can leave a loop in pl/sql by specifying a nice litte label to the loop section.
For example: <<firstloop>>

When labeling, you can exit the loop at any time. In following example, lets give the first loop the label <<firstloop>> and so on...
The third loop contains an exit statement when reaching the value of 2. With this exit the control goes back to the end of the first loop!

Related Posts: