5 Replies Latest reply: Aug 6, 2007 2:10 AM by 577462 RSS

    Difference between CHAR and VARCHAR2 datatype

    Wilhelm
      Difference between CHAR and VARCHAR2 datatype

      CHAR datatype

      If you have an employee name column with size 10; ename CHAR(10) and If a column value 'JOHN' is inserted, 6 empty spaces will be inserted to the right of the value. If this was a VARCHAR column; ename VARCHAR2(10). How would it handle the column value 'JOHN' ?
        • 1. Re: Difference between CHAR and VARCHAR2 datatype
          588622
          Hi Dear,

          Varchar will take 4 spaces out of 10 possible and free the next 6 for other usage.

          Regards
          Amit Rahuvanshi
          • 2. Re: Difference between CHAR and VARCHAR2 datatype
            damorgan
            CHAR is fixed length meaning it will pad your entry with spaces. VARCHAR2 is variable length. Demonstrate this to yourself:
            CREATE TABLE test (
            charcol    CHAR(10),
            varcharcol VARCHAR2(10));

            INSERT INTO test (charcol, varcharcol) VALUES ('A', 'A');

            SELECT LENGTH(charcol), LENGTH(varcharcol) FROM test;
            Other differences relate to their length when used to define table columns: 2K and 4K respectively. When used as PL/SQL both are 32K.
            • 3. Re: Difference between CHAR and VARCHAR2 datatype
              Wilhelm
              If this is the case CHAR datatype will cause space constraints. Then why are we still using CHAR datatype?
              • 4. Re: Difference between CHAR and VARCHAR2 datatype
                426850
                The CHAR datatype stores fixed-length character strings, and Oracle compares CHAR values using blank-padded comparison semantics.

                Where as the VARCHAR2 datatype stores variable-length character strings, and Oracle compares VARCHAR2 values using nonpadded comparison semantics.

                This is important when comparing or joining on the columns having these datatypes;

                SQL*Plus: Release 10.2.0.1.0 - Production on Pzt Au 6 09:16:45 2007

                Copyright (c) 1982, 2005, Oracle. All rights reserved.

                SQL> conn hr/hr
                Connected.
                SQL> set serveroutput on
                SQL> DECLARE
                2 last_name1 VARCHAR2(10) := 'TONGUC';
                3 last_name2 CHAR(10) := 'TONGUC';
                4 BEGIN
                5 IF last_name1 = last_name2 THEN
                6 DBMS_OUTPUT.PUT_LINE ( '-' || last_name1 || '- is equal to -' || last_name2
                || '-');
                7 ELSE
                8 DBMS_OUTPUT.PUT_LINE ( '-' || last_name1 || '- is NOT equal to -' || last_n
                ame2 || '-');
                9 END IF;
                10 END;
                11 /
                -TONGUC- is NOT equal to -TONGUC -

                PL/SQL procedure successfully completed.

                SQL> DECLARE
                2 last_name1 CHAR(6) := 'TONGUC';
                3 last_name2 CHAR(10) := 'TONGUC';
                4 BEGIN
                5 IF last_name1 = last_name2 THEN
                6 DBMS_OUTPUT.PUT_LINE ( '-' || last_name1 || '- is equal to -' || last_name2
                || '-');
                7 ELSE
                8 DBMS_OUTPUT.PUT_LINE ( '-' || last_name1 || '- is NOT equal to -' || last_n
                ame2 || '-');
                9 END IF;
                10 END;
                11 /
                -TONGUC- is equal to -TONGUC -

                PL/SQL procedure successfully completed.

                Also you may want to read related asktom thread - "Char Vs Varchar" http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1542606219593
                and http://tahitiviews.blogspot.com/2007/05/less-is-more-more-or-less.html

                Best regards.
                • 5. Re: Difference between CHAR and VARCHAR2 datatype
                  577462
                  Hi,

                  Just study this u can understood what is the different between VARCHAR, VARCHAR2 AND CHAR

                  http://orafaq.com/faq/what_is_the_difference_between_varchar_varchar2_and_char_data_types

                  Regards

                  Ravi