Wednesday, May 14, 2014

Difference between char(n), char(n char), varchar2(n) and varchar2(n char) data types in PL/SQL

Leave a Comment
In PL/SQL we come across four types of Character/String scalar data types:
1.       Char(n)
2.       Char(n char)
3.       Varchar2(n)
4.       Varchar2(n char)

There is often confusion between these four types of data types. So let’s take up the basic difference among them one by one.

Basic difference between Char(n) and Varchar2(n) data types in Pl/SQL:

1.       Char(n): It is a character data type of fixed length where n specifies the number of bytes or number of characters (in case of single byte characters).

2.       Varchar2(n):  It is a character data type of variable length where n specifies the number of bytes or number of characters (in case of single byte characters).

As we know the definitions now, let me make it more clearly with an example:

Suppose we declare two variables as follows:

v_char char(5):='CHR';
v_varchar varchar2(5):='CHR';
This means  v_char  can hold exactly 5 characters. However, v_varchar can hold upto five characters.

1.       if(v_char=v_varchar) then
dbms_output.put_line('Equal');
else
dbms_output.put_line(' Not Equal');
end if;

Output: ‘Not Equal’ because v_char(5) occupies entire 5 character space i.e. ‘CHR  ‘. Whereas v_varchar is of variable length and out of 5 characters it occupies the required 3 characters i.e. ‘CHR’.

2.       if(v_char='CHR  ') then
dbms_output.put_line('Equal');
else
dbms_output.put_line(' Not Equal');
end if;

Output: ‘Equal’

3.       if(v_varchar='CHR  ') then
dbms_output.put_line('Equal');
else
dbms_output.put_line(' Not Equal');
end if;

Output: ‘Not Equal’

Conclusion:
1.       v_char when compared  holds true for value ‘CHR’, ‘CHR ‘ and ‘CHR  ‘.
2.       V_varchar when compared holds true only for ‘CHR’;

Basic difference between Varchar2(n) and Varchar2(n char) data types in Pl/SQL:

1.       Varchar2(n): It is a character data type of variable length where n specifies the number of bytes or number of characters (in case of single byte characters).

2.       Varchar2(n char):  In case of multi-byte characters, this variable length character data type stores n characters regardless of bytes occupied per character.

     Suppose we declare two variables as follows:
v_name varchar2(20) - v_name can store up to 20 bytes.
v_name1  varchar2(20 char)- v_name1 can store 20 characters regardless of bytes occupied per character.



0 comments:

Post a Comment