7 Replies Latest reply: Nov 13, 2012 10:14 PM by 894936 RSS

    cast and convert

    894936
      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
          CONVERT is for converting character set

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

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

                Thanks
                • 5. Re: cast and convert
                  Gurjeet
                  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
                    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
                      Hi Team
                      Thanks for your support.........