Skip to content
  • YouTube
  • FaceBook
  • Twitter
  • Instagram

Data Analytics Ireland

Data Analytics and Video Tutorials

  • Home
  • Contact
  • About Us
    • Latest
    • Write for us
    • Learn more information about our website
  • Useful Links
  • Glossary
  • All Categories
  • Faq
  • Livestream
  • Toggle search form
  • What is a Clustered Index in SQL? SQL
  • IndexError: single positional indexer is out-of-bounds Index Error
  • What is a Primary Key and Foreign Key SQL
  • What is GITHUB, and should I use it? github
  • How to Add Formulas to Excel using Python numpy
  • How many python functions are there? Python
  • python classes class
  • Tkinter python tutorial Python

What is the difference between CHAR and VARCHAR2 in SQL?

Posted on February 23, 2023March 12, 2023 By admin

Estimated reading time: 2 minutes

In SQL, both CHAR and VARCHAR2 are character data types, but they have some differences in terms of their storage and usage.

CHAR:

  • The CHAR data type is a fixed-length character data type.
  • When you define a column as CHAR, you must specify a fixed length for the data, which is the number of characters that the column can store.
  • If you insert a string into a CHAR column that is shorter than the specified length, it will be padded with spaces to fill the remaining length.
  • The space padding can take up extra storage space, which can be a disadvantage in cases where storage efficiency is important.
  • If you try to do some comparisons, the extra spaces may not yield a match for you.

VARCHAR2:

  • The VARCHAR2 data type is a variable-length character data type.
  • When you define a column as VARCHAR2, you do not need to specify a fixed length. Instead, the length of the data can vary based on the content of the column.
  • VARCHAR2 columns only take up as much space as is needed to store the actual data, which can be an advantage for storage efficiency.
  • If you insert a string into a VARCHAR2 column that is shorter than the maximum length, no padding occurs.

Here’s an example to illustrate the difference:

CREATE TABLE char_test (
   id NUMBER,
   name CHAR(10),
   address CHAR(20)
);

CREATE TABLE varchar2_test (
   id NUMBER,
   name VARCHAR2(10),
   address VARCHAR2(20)
);

INSERT INTO char_test (id, name, address) VALUES (1, 'John', '123 Main St');

INSERT INTO varchar2_test (id, name, address) VALUES (1, 'John', '123 Main St');

In the char_test table, the name and address columns are both defined as CHAR, with fixed lengths of 10 and 20 characters, respectively.

If we insert the name “John” and the address “123 Main St” into the char_test table, the name will be padded with spaces to fill the entire 10-character length of the column, while the address will be padded with spaces to fill the entire 20-character length.

In the varchar2_test table, the name and address columns are both defined as VARCHAR2, with maximum lengths of 10 and 20 characters, respectively.

If we insert the same data into the varchar2_test table, the columns will only take up the actual amount of space needed to store the data, without any padding.

SQL, Working with databases Tags:Data, data types

Post navigation

Previous Post: What is the difference between SQL and MySQL?
Next Post: What Are Constraints in SQL?

Related Posts

  • What is a Unique key in SQL? SQL
  • how to select columns with SQL SQL
  • What is Apache Spark and what is it used for? Apache Spark
  • What is the difference between SQL and MySQL? SQL
  • What is a Clustered Index in SQL? SQL
  • What is the difference between DROP and TRUNCATE in SQL? SQL

Select your language!

  • हिंदी
  • Español
  • Português
  • Français
  • Italiano
  • Deutsch
  • how to use case statements in SQL SQL
  • Python tutorial: Create an input box in Tkinter Python
  • How to Pass Python Variables to Javascript Javascript
  • ValueError: Columns must be same length as key exception handling
  • How to Create an XML file from Excel using Python Python
  • how to select columns with SQL SQL
  • Python Tutorial: Add a column to a data frame Python Dataframe
  • planning your machine learning model machine learning

Copyright © 2023 Data Analytics Ireland.

Powered by PressBook Premium theme

This website uses cookies to improve your experience. We'll assume you're ok with this, but you can opt-out if you wish. Cookie settingsACCEPT
Privacy & Cookies Policy

Privacy Overview

This website uses cookies to improve your experience while you navigate through the website. Out of these cookies, the cookies that are categorized as necessary are stored on your browser as they are essential for the working of basic functionalities of the website. We also use third-party cookies that help us analyze and understand how you use this website. These cookies will be stored in your browser only with your consent. You also have the option to opt-out of these cookies. But opting out of some of these cookies may have an effect on your browsing experience.
Necessary
Always Enabled
Necessary cookies are absolutely essential for the website to function properly. This category only includes cookies that ensures basic functionalities and security features of the website. These cookies do not store any personal information.
Non-necessary
Any cookies that may not be particularly necessary for the website to function and is used specifically to collect user personal data via analytics, ads, other embedded contents are termed as non-necessary cookies. It is mandatory to procure user consent prior to running these cookies on your website.
SAVE & ACCEPT