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#LNPLS015Code 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;
Akdora's Blog 
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!