Difference Between CHAR, VARCHAR, and VARCHAR2
FREE
Overview
When working with database systems, selecting the most appropriate data type is crucial. In this article, we will discuss three different types of character string data types in SQL - CHAR, VARCHAR, and VARCHAR2.
This article will discuss the differences in these character string data types to make informed decisions when designing the database schema.
CHAR Datatype
The CHAR data type is used to store character string values. The maximum length of a CHAR value is 256 characters in MySQL and 2000 bytes in the Oracle database. It is used to store strings of alphanumeric and special characters.
When we define a CHAR column in a table, we have to specify a fixed length for the column. All the values stored in that column will have the same length as specified, the shorter values are padded automatically with spaces or fillers to meet the specified length of the CHAR column.
Syntax of CHAR
Placeholder char_name is the name of the column CHAR that has a size of length characters/bytes. If we don't specify the length semantics, it will default to character length semantics.
If we define a CHAR column without specifying a length, Oracle will assign a length of 1 character (or byte, depending on the character set) by default.
Example of CHAR
Let us create a table of a single CHAR column to see how the padding works.
SQL Query:
Creating a table CharExample.
Inserting values into the table.
Now we will select the values and check the length of each value.
Output:
| char_column | padded_char_column | char_column_length |
|---|---|---|
| PQRST | (PQRST ) | 10 |
| ABCDE | (ABCDE ) | 10 |
| LearnSQL | (LearnSQL ) | 10 |
In the above example, we are querying the char_column column twice, first with the original value, and second with a prefix of open parentheses and a suffix of close parentheses. The second column, i.e., padded_char_column demonstrates the original length of the CHAR values by showing the padded spaces in the value.
This can also be confirmed by looking at the char_column_length column which shows the length of each char_column value to be 10 characters.
NOTE: Fillers are custom padding characters in place of the blank spaces. We can specify fillers using the CHAR(N) DEFAULT 'X' syntax, where N is the size of the character string and X is the custom filler.
VARCHAR Datatype
The VARCHAR data type is used to store variable-length character string values. The maximum length of a VARCHAR value is 2000 bytes in the Oracle database. It is used to store strings of alphanumeric and special characters.
Unlike CHAR, which has a fixed length, VARCHAR can contain strings of varying lengths, and it does not pad values shorter than the length specified while defining a VARCHAR column. The length of the VARCHAR value will be the number of characters in it, not the specified maximum length.
Syntax of VARCHAR
Placeholder varchar_name is the name of the column VARCHAR that has a maximum size of length characters/bytes. If we don't specify the length semantics, it will default to character length semantics.
If we define a VARCHAR column without specifying a length, Oracle will generate an error, unlike CHAR, that gets specified a length of 1 character/byte by default in the absence of a length specification.
Example of VARCHAR
Let us create a table of a single VARCHAR column to see the length of values shorter than the specified length.
SQL Query:
Creating a table VarcharExample.
Inserting values into the table.
Now we will select the values and check the length of each value.
Output:
| varchar_column | varchar_column_length |
|---|---|
| PQRST | 6 |
| ABCDE | 5 |
| Learn SQL | 9 |
In the above example, we are querying the varchar_column values and the length of those values and we can see that the length of these values is less than the specified length and equal to the number of characters present. We can also see that the trailing space in PQRST was counted as a character totalling 6 characters.
VARCHAR2 Datatype
The VARCHAR2 data type is also used to store variable-length character string values in the Oracle database. The maximum length of a VARCHAR value is 4000 bytes in the Oracle database. It is used to store strings of alphanumeric and special characters.
Unlike CHAR, which has a fixed length, VARCHAR2 can contain strings of varying lengths, and it does not pad values shorter than the length specified while defining a VARCHAR2 column. The length of the VARCHAR2 value will be the number of characters in it, not the specified maximum length. This behaviour is pretty much similar to VARCHAR.
Syntax of VARCHAR2
Placeholder varchar2_name is the name of the column VARCHAR2 that has a maximum size of length characters/bytes. If we don't specify the length semantics, it will default to character length semantics.
Example of VARCHAR2
Let us create a table of a single VARCHAR2 column to see the length of values shorter than the specified length.
SQL Query:
Creating a table Varchar2Example.
Inserting values into the table.
Now we will select the values and check the length of each value.
Output:
| varchar2_column | varchar2_column_length |
|---|---|
| PQRST | 7 |
| ABCDE | 5 |
| Learn SQL | 14 |
In the above example, we are querying the varchar2_column values and the length of those values and we can see that the length of these values is less than the specified length and equal to the number of characters present. We can also see that the trailing spaces in PQRST and Learn SQL were counted as characters totalling 7 and 14 characters respectively.
When not nitpicking, you will find there are little to no differences between VARCHAR and VARCHAR2, but we will discuss them for the sake of your curiosity, in the next section!
Difference between CHAR, VARCHAR, and VARCHAR2
CHAR, VARCHAR, and VARCHAR2 may seem to be similar, but there are some noticeable differences in their functioning, application, and efficiency.
| Aspect | CHAR | VARCHAR | VARCHAR2 |
|---|---|---|---|
| Standard | ANSI SQL standard | ANSI SQL standard | Oracle standard |
| Support | Supported by a variety of relational database management systems | Supported by a variety of relational database management systems | Specific to the Oracle Database System |
| Maximum Length | Maximum length is characters in MySQL, and bytes of character data in Oracle database | Can store maximum bytes of character data | Can store maximum bytes of character data |
| Data Type Length | CHAR is fixed length | VARCHAR is variable length | VARCHAR2 is variable length |
| Storage Allocation | All the values stored in a CHAR column will have the same length as specified | Allocated space is equal to the actual data length which can be less than the specified data length | Allocated space is equal to the actual data length which can be less than the specified data length |
| Padding | Padding on the right side of strings with blank spaces or fillers | No padding | No padding |
| Efficiency | Less space efficient, storage is wasted for values shorter than the specified length | More space efficient for variable lengths | More space efficient for variable lengths |
| Data Integrity | Treats NULL values and empty string differently | Treats NULL values and empty string differently | Doesn't distinguish between NULL values and empty strings |
| Common Usage | In conditions when the data is required to be of a fixed length, like phone number | Used for variable length data, like name of a person | Used for variable length data, like name of a person |
| Indexing | Large index structures as they have fixed length keys for indexing | Smaller index structures as they have variable length keys | Similarly to VARCHAR, index structures are smaller in VARCHAR2 as they have variable length keys |
| Example | CHAR(20) "COVFEFE" is "COVFEFE " | VARCHAR(20) "COVFEFE" is "COVFEFE" | VARCHAR2(20) "COVFEFE" is "COVFEFE" |
Conclusion
- CHAR, VARCHAR, and VARCHAR2 are data types for storing character string values.
- CHAR is a fixed-length data type that pads values with spaces/fillers to meet the specified length.
- VARCHAR and VARCHAR2 are variable length data types that do not pad values. The length of a value in CHAR/VARCHAR is equal to the number of characters in it or bytes of space occupied by it.
- CHAR and VARCHAR are of ANSI SQL standard that is supported by many database systems, whereas, VARCHAR2 is specific to the Oracle database only.
- CHAR can be chosen to maintain data integrity by enforcing fixed length values, otherwise, VARCHAR and VARCHAR2 are used for variable length values that optimize storage and efficiency.