08 August, 2012

CHAR vs VARCHAR vs VARCHAR2


Today I will present short notes about popular string data types in SQL and PLSQL.

CHAR
it is used for storing fix length character strings. String values will be space/blank padded before stored on disk. If this type is used to store variable length strings, it will waste a lot of disk space.
Example
 CREATE TABLE CHAR_TAB (CHAR_COL CHAR (10));  

 INSERT INTO CHAR_TAB (CHAR_COL)  
    VALUES ('Mahmoud');  
 COMMIT;  

Let's now select data and lenght of data and dump of data in table CHAR_TAB
 SELECT CHAR_COL, LENGTH (CHAR_COL), DUMP (CHAR_COL) FROM CHAR_TAB;  

The output will be like
As We noticed in result that length is 10 characters inspire of I entered 'Mahmoud' which has only 7 characters.
We also noticed that in dump he padded at latest ASCII code three times number 32 which is ASCII code of space.



VARCHAR
VARCHAR work as varchar2 but this type shouldn't use as it is reserved for future usage.
Example
 CREATE TABLE VARCHAR_TAB (VARCHAR_COL VARCHAR (10));  

 INSERT INTO VARCHAR_TAB (VARCHAR_COL)  
    VALUES ('Mahmoud');  
 COMMIT;  

Let's now select data and length of data and dump of data in table CHAR_TAB
 SELECT VARCHAR_COL, LENGTH (VARCHAR_COL), DUMP (VARCHAR_COL) FROM VARCHAR_TAB; 
 
The output will be like 
As we noticed that it stored 7 characters in spite of the length of column in table is 10 characters.

VARCHAR2
It is used to store variable length character strings. The string value's length will be stored on disk with the value itself.
Example
 CREATE TABLE VARCHAR2_TAB (VARCHAR2_COL VARCHAR2 (10));  

 INSERT INTO VARCHAR2_TAB (VARCHAR2_COL)  
    VALUES ('Mahmoud');  
 COMMIT;  

Let's now select data and length of data and dump of data in table CHAR_TAB
 SELECT VARCHAR2_COL, LENGTH (VARCHAR2_COL), DUMP (VARCHAR2_COL
 FROM VARCHAR2_TAB;  

The output will be like


Thanks

No comments:

Post a Comment

ADF : Scope Variables

Oracle ADF uses many variables and each variable has a scope. There are five scopes in ADF (Application, Request, Session, View and PageFl...