Akdora’s Blog

Programming, Oracle, Life, Fun

Oracle VARCHAR2 vs. CHAR February 9, 2009

Filed under: Oracle,PL/SQL — Akdora @ 8:21 am
Tags: , , , , , , ,

In Oracle, If we want to store some string, we uses VARCHAR2 type or its subtypes.

SQL> set serveroutput on
SQL> desc sys.standard;
Element                          Type     
-------------------------------- ---------
......
VARCHAR2                         TYPE     
VARCHAR                          SUBTYPE  
STRING                           SUBTYPE  
LONG                             SUBTYPE  
RAW                              SUBTYPE  
"LONG RAW"                       SUBTYPE  
ROWID                            SUBTYPE  
CHAR                             SUBTYPE  
CHARACTER                        SUBTYPE  
......
SQL>

Most commonly used ones of these types are VARCHAR2 and CHAR types. In this post, we will compare them.

Connected to Oracle Database 10g Express Edition Release 10.2.0.1.0
Connected as hr
 
SQL> set serveroutput on
SQL>
SQL> CREATE OR REPLACE PROCEDURE prc_compare_varchar2_char IS
  2       v_char_ex1     CHAR(4) := 'TEST';
  3       v_varchar2_ex1 VARCHAR2(4) := 'TEST';
  4 
  5       v_char_ex2     CHAR(40) := 'TEST';
  6       v_varchar2_ex2 VARCHAR2(40) := 'TEST';
  7  BEGIN
  8  
  9       dbms_output.put_line('------------------------v_char_ex1 vs. v_varchar2_ex1----------------------------');
 10       dbms_output.put_line('');
 11       IF v_char_ex1 = v_varchar2_ex1
 12       THEN
 13            dbms_output.put_line('In EX:1 >>> CHAR = VARCHAR2, Both of them has 4 character and 4 length!');
 14       ELSE
 15            dbms_output.put_line('In EX:1 >>> CHAR != VARCHAR2, Both of them has 4 character and 4 length!');
 16       END IF;
 17       dbms_output.put_line('');
 18       dbms_output.put_line('v_varchar2_ex1 : #' || v_varchar2_ex1 || '#');
 19       dbms_output.put_line('v_char_ex1     : #' || v_char_ex1 || '#');
 20       dbms_output.put_line('');
 21       dbms_output.put_line('------------------------v_char_ex2 vs. v_varchar2_ex2----------------------------');
 22       dbms_output.put_line('');
 23 
 24       IF v_char_ex2 = v_varchar2_ex2
 25       THEN
 26            dbms_output.put_line('In EX:2 >>> CHAR = VARCHAR2, Both of them has 4 character and 40 length!');
 27       ELSE
 28            dbms_output.put_line('In EX:2 >>> CHAR != VARCHAR2, Both of them has 4 character and 40 length!');
 29 
 30       END IF;
 31 
 32       dbms_output.put_line('');
 33       dbms_output.put_line('v_varchar2_ex2 : #' || v_varchar2_ex2 || '#');
 34       dbms_output.put_line('v_char_ex2     : #' || v_char_ex2 || '#');
 35 
 36       dbms_output.put_line('');
 37       dbms_output.put_line('-----------------------v_char_ex1 vs. v_char_ex2-----------------------------------');
 38       dbms_output.put_line('');
 39       IF v_char_ex1 = v_char_ex2
 40       THEN
 41            dbms_output.put_line('In EX:3 >>> CHAR(4) = CHAR(40), Both of them has 4 character!');
 42       ELSE
 43            dbms_output.put_line('In EX:3 >>> CHAR(4) != CHAR(40), Both of them has 4 character!');
 44       END IF;
 45       dbms_output.put_line('');
 46       dbms_output.put_line('v_char_ex1     : #' || v_char_ex1 || '#');
 47       dbms_output.put_line('v_char_ex2     : #' || v_char_ex2 || '#');
 48 
 49       dbms_output.put_line('');
 50  END;
 51  /
 
Procedure created
 
SQL>
SQL> begin
  2    prc_compare_varchar2_char;
  3  end;
  4  /
 
------------------------v_char_ex1 vs. v_varchar2_ex1----------------------------
 
In EX:1 >>> CHAR = VARCHAR2, Both of them has 4 character and 4 length!
 
v_varchar2_ex1 : #TEST#
v_char_ex1     : #TEST#
 
------------------------v_char_ex2 vs. v_varchar2_ex2----------------------------
 
In EX:2 >>> CHAR != VARCHAR2, Both of them has 4 character and 40 length!
 
v_varchar2_ex2 : #TEST#
v_char_ex2     : #TEST                                    #
 
-----------------------v_char_ex1 vs. v_char_ex2-----------------------------------
 
In EX:3 >>> CHAR(4) = CHAR(40), Both of them has 4 character!
 
v_char_ex1     : #TEST#
v_char_ex2     : #TEST                                    #
 
 
PL/SQL procedure successfully completed
 
SQL>

 As you see, If the character length that stored in the type is equal the length of the type, then VARHCAR2 and CHAR is equal to each other. Otherwise, they are not equal to each other. Because CHAR type add spaces to end of the string to reach to the type length. On the other hand, If we compare CHAR type in different lengths and they store the same word(s), they are equal.

In this case, If we want to compare a VARCHAR2 ana CHAR type. We have to use TRIM! or RTRIM will be more better choice🙂

SQL> DECLARE
  2       v_char_ex2     CHAR(40) := 'TEST';
  3       v_varchar2_ex2 VARCHAR2(40) := 'TEST';
  4  BEGIN
  5 
  6       IF RTRIM(v_char_ex2) = v_varchar2_ex2
  7       THEN
  8            dbms_output.put_line('In EX:4 >>> CHAR = VARCHAR2, Both of them has 4 character and 40 length!');
  9       ELSE
 10            dbms_output.put_line('In EX:4 >>> CHAR != VARCHAR2, Both of them has 4 character and 40 length!');
 11 
 12       END IF;
 13 
 14       dbms_output.put_line('');
 15       dbms_output.put_line('v_char_ex2        :' || '#' || v_char_ex2 || '#');
 16       dbms_output.put_line('RTRIM(v_char_ex2) :' || '#' || RTRIM(v_char_ex2) || '#');
 17       dbms_output.put_line('v_varchar2_ex2    :' || '#' || v_varchar2_ex2 || '#');
 18  END;
 19  /
 
In EX:4 >>> CHAR = VARCHAR2, Both of them has 4 character and 40 length!
 
v_char_ex2        :#TEST                                    #
RTRIM(v_char_ex2) :#TEST#
v_varchar2_ex2    :#TEST#
 
PL/SQL procedure successfully completed
 
SQL>

 Choosing Between the CHAR and VARCHAR2 Datatypes:

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14251/adfns_sqltypes.htm#sthref372

If you want to convert a data column from VARCHAR2 to CHAR, we have to care about PCTUSED, PCTFREE parameters. We can specify this parameters, when we create the table.  

After a large update, if rows fill over to the percentage of the  PCTFREE parameter. Then, oracle continues to write the data of the current row in a new row. The second row’s rowid index to the firsts row. So, when you try this row, you actually have to read two rows!!  This event called “Row Chaining”.

Check out following documentation:

http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/logical.htm#sthref344

 

Suggestions about this topic:

http://tonguc.wordpress.com/2007/03/31/oracle-native-types-part-2/
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/datatypes.htm#LNPLS015
Code Listing:
CREATE OR REPLACE PROCEDURE prc_compare_varchar2_char IS
     v_char_ex1     CHAR(4) := 'TEST';
     v_varchar2_ex1 VARCHAR2(4) := 'TEST';
     v_char_ex2     CHAR(40) := 'TEST';
     v_varchar2_ex2 VARCHAR2(40) := 'TEST';
BEGIN
     dbms_output.put_line('------------------------v_char_ex1 vs. v_varchar2_ex1----------------------------');
     dbms_output.put_line('');
     IF v_char_ex1 = v_varchar2_ex1
     THEN
          dbms_output.put_line('In EX:1 >>> CHAR = VARCHAR2, Both of them has 4 character and 4 length!');
     ELSE
          dbms_output.put_line('In EX:1 >>> CHAR != VARCHAR2, Both of them has 4 character and 4 length!');
     END IF;
     dbms_output.put_line('');
     dbms_output.put_line('v_varchar2_ex1 : #' || v_varchar2_ex1 || '#');
     dbms_output.put_line('v_char_ex1     : #' || v_char_ex1 || '#');
     dbms_output.put_line('');
     dbms_output.put_line('------------------------v_char_ex2 vs. v_varchar2_ex2----------------------------');
     dbms_output.put_line('');
     IF v_char_ex2 = v_varchar2_ex2
     THEN
          dbms_output.put_line('In EX:2 >>> CHAR = VARCHAR2, Both of them has 4 character and 40 length!');
     ELSE
          dbms_output.put_line('In EX:2 >>> CHAR != VARCHAR2, Both of them has 4 character and 40 length!');
    
     END IF;
     dbms_output.put_line('');
     dbms_output.put_line('v_varchar2_ex2 : #' || v_varchar2_ex2 || '#');
     dbms_output.put_line('v_char_ex2     : #' || v_char_ex2 || '#');
     dbms_output.put_line('');
     dbms_output.put_line('-----------------------v_char_ex1 vs. v_char_ex2-----------------------------------');
     dbms_output.put_line('');
     IF v_char_ex1 = v_char_ex2
     THEN
          dbms_output.put_line('In EX:3 >>> CHAR(4) = CHAR(40), Both of them has 4 character!');
     ELSE
          dbms_output.put_line('In EX:3 >>> CHAR(4) != CHAR(40), Both of them has 4 character!');
     END IF;
     dbms_output.put_line('');
     dbms_output.put_line('v_char_ex1     : #' || v_char_ex1 || '#');
     dbms_output.put_line('v_char_ex2     : #' || v_char_ex2 || '#');
     dbms_output.put_line('');
END;
------------------
DECLARE
     v_char_ex2     CHAR(40) := 'TEST';
     v_varchar2_ex2 VARCHAR2(40) := 'TEST';
BEGIN
     IF RTRIM(v_char_ex2) = v_varchar2_ex2
     THEN
          dbms_output.put_line('In EX:4 >>> CHAR = VARCHAR2, Both of them has 4 character and 40 length!');
     ELSE
          dbms_output.put_line('In EX:4 >>> CHAR != VARCHAR2, Both of them has 4 character and 40 length!');
    
     END IF;
     dbms_output.put_line('');
     dbms_output.put_line('v_char_ex2        :' || '#' || v_char_ex2 || '#');
     dbms_output.put_line('RTRIM(v_char_ex2) :' || '#' || RTRIM(v_char_ex2) || '#');
     dbms_output.put_line('v_varchar2_ex2    :' || '#' || v_varchar2_ex2 || '#');
END;
 
 

One Response to “Oracle VARCHAR2 vs. CHAR”

  1. Des Says:

    Thanks very much, I’ve been looking for how to compare a char with a varchar (or vice versa) and this is the only place that mentioned RTRIM, everyone else was saying to change the type within the table from char to varchar .. etc..

    Thanks!


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s