This discussion is archived
7 Replies Latest reply: Nov 13, 2012 8:14 PM by 894936 RSS

cast and convert

894936 Newbie
Currently Being Moderated
Hi Team,

could you please tell me where do we use cast and convert functions.
What is the main difference between these two with examples.

Thanks in advance.
Mamatha
  • 1. Re: cast and convert
    jeneesh Guru
    Currently Being Moderated
    CONVERT is for converting character set

    CAST is for changing the data type..
  • 2. Re: cast and convert
    Gurjeet Newbie
    Currently Being Moderated
    http://www.simple-talk.com/sql/t-sql-programming/data-conversion-in-sql-server/
  • 3. Re: cast and convert
    jeneesh Guru
    Currently Being Moderated
    CONVERT in ORACLE and SQLServer are not similar, I feel..
  • 4. Re: cast and convert
    894936 Newbie
    Currently Being Moderated
    Hi,

    could you pls elaborate.......i want these differences in Oracle.

    Thanks
  • 5. Re: cast and convert
    Gurjeet Newbie
    Currently Being Moderated
    cast function converts one datatype to another.

    select cast( '27-Oct-2011' AS varchar2(30) ) from dual;


    convert function converts a string from one character set to another.

    convert('A B C D E Ä Ê Í Õ Ø', 'US7ASCII', 'WE8ISO8859P1')     from dual;
  • 6. Re: cast and convert
    jeneesh Guru
    Currently Being Moderated
    I do remember using CAST when creating views with NULL values as columns (For some purpose it was required).

    If you dont use CAST when used null as column, the view column will be created as VARCHAR2(0). This will be an issue in some reporting tools.

    So using CATS you can change the data type to NUMBER or VARCHAR(50) (Example)
    SQL> create or replace view v1 as select null as col1 from dual;
    
    View created.
    
    SQL> desc v1
     Name                                      Null?    Type
     ----------------------------------------- -------- ----------------------------
     COL1                                               VARCHAR2
    
    SQL> create or replace view v1 as select cast(null as varchar2(50)) as  col1 from dual;
    
    View created.
    
    SQL> desc v1
     Name                                      Null?    Type
     ----------------------------------------- -------- ----------------------------
     COL1                                               VARCHAR2(50)
    CAST is also used with MULTISET, when working with OBJECT TYPEs..
    Read Here

    CONVERT is used when you want to see a particular text or column in a diffrent character set than the stored one. I have never used it , though...

    Edited by: jeneesh on Oct 12, 2012 6:05 PM
  • 7. Re: cast and convert
    894936 Newbie
    Currently Being Moderated
    Hi Team
    Thanks for your support.........

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points