Char, Varchar, Varchar2

Question

Hi... I want to know difference between Char , Varchar , Varchar2 with example ? Thanks....

Datta Kharad · Answer

Hi Difference Between Char,Varchar and Varchar2 The CHAR and VARCHAR data types store non-binary strings (that is, strings of characters that have a character set and collation). These types differ in terms of their maximum allowable length and in how trailing spaces are handled. The CHAR data type is a fixed-length type. The length should be a number from 0 to 255. The CHAR data type holds strings up to the length specified in the column definition. Values in a CHAR column always take the same amount of storage. For example, a column defined as CHAR(30) requires 30 characters for each value, even empty values. Values shorter than the designated length are padded with spaces to that length when they are stored. Trailing spaces are removed from CHAR values when they are retrieved, so retrieved values might not be the same length as when stored. VARCHAR is a variable-length data type. VARCHAR columns are defined similarly to CHAR columns, but the maximum length can be a number up to 65,535. (The actual allowable maximum length is a few characters less due to internal restrictions imposed by storage engines.) A string stored into a VARCHAR column takes only the number of characters required to store it, plus one or two bytes to record the string's length. (One byte for columns declared with a length less than 256, two bytes otherwise.) Values in a VARCHAR column are stored as given. Trailing spaces are not removed or added for storage or retrieval. VARCHAR2 is used to store variable length character strings. The string value's length will be stored on disk with the value itself. Main difference between Varchar and Varchar2 is capacity(size) of character, Varchar Min Size 1 Byte to Max Size 2000 Bytes and Varchar2 Min Size 1 Byte to Max Size 4000 Byte. With CHAR data type column, search seems working faster than VARCHAR data type column. Example of Char :- SQL> create table char_test (col1 CHAR(10)); Table created. SQL> insert into char_test values ('qwerty'); 1 row created. COL1 LENGTH(COL1) ASCII Dump ------ ----------- ---------------------------------------------------------- qwerty 10 Typ=96 Len=10: 113,119,101,114,116,121,32,32,32,32 Example of Varchar :- SQL> create table varchar_test (col1 varchar2(10)); Table created. SQL> insert into varchar_test values ('qwerty'); 1 row created. SQL> select col1, length(col1), dump(col1) "ASCII Dump" from varchar_test; COL1 LENGTH(COL1) ASCII Dump ------- ------------ -------------------------------------------------- qwerty 6 Typ=1 Len=6: 113,119,101,114,116,121 Example of Varchar2 :- SQL> create table varchar2_test (col1 varchar2(10)); Table created. SQL> insert into varchar2_test values ('qwerty'); 1 row created. SQL> select col1, length(col1), dump(col1) "ASCII Dump" from varchar2_test; COL1 LENGTH(COL1) ASCII Dump ---------- ------------------ ------------------------------------------ qwerty 6 Typ=1 Len=6: 113,119,101,114,116,121 Please mark as Accepted answer if your query resolved.

Vineet Kumar Saini · Answer

Thanks all my Dears...............

Aarti · Answer

Hi Vineet, VARCHAR and VARCHAR2 are the same, but the function of VARCHAR might change in future. Use VARCHAR2. CHAR and VARCHAR2 are different in that CHAR blank pads its strings to be of its maximum length. VARCHAR2 does no blank padding. Because of the above, queries that involve string comparison on a CHAR column require more care – a VARCHAR2 compared to CHAR needs to be rpad-ded to get a hit. It bears repeating: use VARCHAR2! Now When should we use CHAR, when VARCHAR2? If you only ever use VARCHAR2 and ignore CHAR, you will make life much simpler. There is no difference between the two except that CHAR uses up more space when your strings are not always of the fixed maximum length. Plus, CHAR leads to more confusion in writing queries. I use CHAR as database columns only for Y/N type of values. (There is no BOOLEAN column datatype, remember?) This acts like a marker for a "flag" or "switch" type of column – but it could equally well have been VARCHAR2. For all other strings, it's VARCHAR2. Thanks..

Satyapriya Nayak · Answer

Hi Vineet, Normal 0 Char - We should be use when you know that the number of characters in the column field is constant .ex if the gender is column with char (1)- so only M and F are the two options so char (1) is better to use than varchar2. Varchar2 - first of all varchar2 is an oracle standard.varchar2 is nothing but variable characters. Ex- If you have defined the column with varchar2 (50) and when you are inserting the record into that column with only 10 letter/characters then only 10 bits will be used instead of 50.If you declare char (50) full 50 bits is allocated for that column. So even if u enter on 20 characters the space utilized will be full 50 bits. Thanks If this post helps you mark it as answer

Alok Pandey · Answer

Hi,Vineet.. see this link, http://www.orafaq.com/faq/what_is_the_difference_between_varchar_varchar2_and_char_data_types

Char, Varchar, Varchar2

Answers (5)