[sql] Difference between BYTE and CHAR in column datatypes

In Oracle, what is the difference between :



 NAME VARCHAR2(11 CHAR), -- or even VARCHAR2(11)

This question is related to sql oracle unicode varchar

The answer is

Let us assume the database character set is UTF-8, which is the recommended setting in recent versions of Oracle. In this case, some characters take more than 1 byte to store in the database.

If you define the field as VARCHAR2(11 BYTE), Oracle can use up to 11 bytes for storage, but you may not actually be able to store 11 characters in the field, because some of them take more than one byte to store, e.g. non-English characters.

By defining the field as VARCHAR2(11 CHAR) you tell Oracle it can use enough space to store 11 characters, no matter how many bytes it takes to store each one. A single character may require up to 4 bytes.

One has exactly space for 11 bytes, the other for exactly 11 characters. Some charsets such as Unicode variants may use more than one byte per char, therefore the 11 byte field might have space for less than 11 chars depending on the encoding.

See also http://www.joelonsoftware.com/articles/Unicode.html

Depending on the system configuration, size of CHAR mesured in BYTES can vary. In your examples:

  1. Limits field to 11 BYTE
  2. Limits field to 11 CHARacters

Conclusion: 1 CHAR is not equal to 1 BYTE.

I am not sure since I am not an Oracle user, but I assume that the difference lies when you use multi-byte character sets such as Unicode (UTF-16/32). In this case, 11 Bytes could account for less than 11 characters.

Also those field types might be treated differently in regard to accented characters or case, for example 'binaryField(ete) = "été"' will not match while 'charField(ete) = "été"' might (again not sure about Oracle).

Questions with sql tag:

Passing multiple values for same variable in stored procedure SQL permissions for roles Generic XSLT Search and Replace template Access And/Or exclusions Pyspark: Filter dataframe based on multiple conditions Subtracting 1 day from a timestamp date PYODBC--Data source name not found and no default driver specified select rows in sql with latest date for each ID repeated multiple times ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database Export result set on Dbeaver to CSV How to create temp table using Create statement in SQL Server? SQL Query Where Date = Today Minus 7 Days How do I pass a list as a parameter in a stored procedure? #1273 – Unknown collation: ‘utf8mb4_unicode_520_ci’ MySQL Error: : 'Access denied for user 'root'@'localhost' SQL Server IF EXISTS THEN 1 ELSE 2 How to add a boolean datatype column to an existing table in sql? Presto SQL - Converting a date string to date format What is the meaning of <> in mysql query? Change Date Format(DD/MM/YYYY) in SQL SELECT Statement Convert timestamp to date in Oracle SQL #1292 - Incorrect date value: '0000-00-00' Postgresql tables exists, but getting "relation does not exist" when querying SQL query to check if a name begins and ends with a vowel Find the number of employees in each department - SQL Oracle Error in MySQL when setting default value for DATE or DATETIME Drop view if exists Could not find server 'server name' in sys.servers. SQL Server 2014 How to create a Date in SQL Server given the Day, Month and Year as Integers TypeError: tuple indices must be integers, not str Select Rows with id having even number SELECT list is not in GROUP BY clause and contains nonaggregated column IN vs ANY operator in PostgreSQL How to insert date values into table Error related to only_full_group_by when executing a query in MySql How to select the first row of each group? Connecting to Microsoft SQL server using Python eloquent laravel: How to get a row count from a ->get() How to execute raw queries with Laravel 5.1? In Oracle SQL: How do you insert the current date + time into a table? Extract number from string with Oracle function Rebuild all indexes in a Database SQL: Two select statements in one query DB2 SQL error sqlcode=-104 sqlstate=42601 What difference between the DATE, TIME, DATETIME, and TIMESTAMP Types How to run .sql file in Oracle SQL developer tool to import database? Concatenate columns in Apache Spark DataFrame How Stuff and 'For Xml Path' work in SQL Server? Fatal error: Call to a member function query() on null

Questions with oracle tag:

concat yesterdays date with a specific time ORA-28001: The password has expired how to modify the size of a column How to create a blank/empty column with SELECT query in oracle? Find the number of employees in each department - SQL Oracle Query to display all tablespaces in a database and datafiles When or Why to use a "SET DEFINE OFF" in Oracle Database How to insert date values into table error: ORA-65096: invalid common user or role name in oracle In Oracle SQL: How do you insert the current date + time into a table? Extract number from string with Oracle function How to run .sql file in Oracle SQL developer tool to import database? How to kill all active and inactive oracle sessions for user What does it mean when the size of a VARCHAR2 in Oracle is declared as 1 byte? How to subtract hours from a date in Oracle so it affects the day also Why does Oracle not find oci.dll? Oracle SQL - DATE greater than statement How to solve : SQL Error: ORA-00604: error occurred at recursive SQL level 1 How do I view the Explain Plan in Oracle Sql developer? Getting Error - ORA-01858: a non-numeric character was found where a numeric was expected SQLPLUS error:ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA How to change Oracle default data pump directory to import dumpfile? How can you tell if a value is not numeric in Oracle? Why do I get PLS-00302: component must be declared when it exists? Split function in oracle to comma separated values with automatic sequence DATEDIFF function in Oracle How can I solve ORA-00911: invalid character error? sqlplus: error while loading shared libraries: libsqlplus.so: cannot open shared object file: No such file or directory Windows cannot find 'http:/.'. Make sure you typed the name correctly, and then try again How do I resolve this "ORA-01109: database not open" error? ORA-01653: unable to extend table by in tablespace ORA-06512 How to Select Top 100 rows in Oracle? How to determine tables size in Oracle Split String by delimiter position using oracle SQL ORA-28000: the account is locked error getting frequently Oracle Add 1 hour in SQL Oracle listener not running and won't start PLS-00428: an INTO clause is expected in this SELECT statement ORA-01652: unable to extend temp segment by 128 in tablespace SYSTEM: How to extend? What is the max size of VARCHAR2 in PL/SQL and SQL? How to configure Glassfish Server in Eclipse manually Oracle query to identify columns having special characters The listener supports no services ORA-00907: missing right parenthesis NLS_NUMERIC_CHARACTERS setting for decimal copy from one database to another using oracle sql developer - connection failed Oracle Partition - Error ORA14400 - inserted partition key does not map to any partition Equivalent function for DATEADD() in Oracle Oracle SQL Developer: Failure - Test failed: The Network Adapter could not establish the connection? BadImageFormatException. This will occur when running in 64 bit mode with the 32 bit Oracle client components installed

Questions with unicode tag:

How to resolve TypeError: can only concatenate str (not "int") to str (unicode error) 'unicodeescape' codec can't decode bytes in position 2-3: truncated \UXXXXXXXX escape UnicodeEncodeError: 'ascii' codec can't encode character at special name Python NLTK: SyntaxError: Non-ASCII character '\xc3' in file (Sentiment Analysis -NLP) HTML for the Pause symbol in audio and video control Javascript: Unicode string to hex Concrete Javascript Regex for Accented Characters (Diacritics) Replace non-ASCII characters with a single space UTF-8 in Windows 7 CMD NameError: global name 'unicode' is not defined - in Python 3 UnicodeEncodeError: 'ascii' codec can't encode character u'\xe9' in position 7: ordinal not in range(128) Representing Directory & File Structure in Markdown Syntax What's the difference between ASCII and Unicode? Saving utf-8 texts with json.dumps as UTF8, not as \u escape sequence Easy way to convert a unicode list to a list containing python strings? Python unicode equal comparison failed UnicodeDecodeError when reading CSV file in Pandas with Python Python str vs unicode types Invisible characters - ASCII Byte and char conversion in Java u'\ufeff' in Python string Font Awesome & Unicode Read and Write CSV files including unicode with Python 2.7 CSS: how to add white space before element's content? Convert Unicode data to int in python SSIS Convert Between Unicode and Non-Unicode Error python encoding utf-8 What is the HTML unicode character for a "tall" right chevron? Unicode via CSS :before How to make Unicode charset in cmd.exe by default? Decode UTF-8 with Javascript How to replace unicode characters in string with something else python? Insert Unicode character into JavaScript What would be the Unicode character for big bullet in the middle of the character? How to read text files with ANSI encoding and non-English letters? Is there Unicode glyph Symbol to represent "Search" How to print Unicode character in C++? How to convert a UTF-8 string into Unicode? How to convert a string with Unicode encoding to a string of letters How to find the length of a string in R How to remove \xa0 from string in Python? SyntaxError: Non-ASCII character '\xa3' in file when function returns '£' UnicodeDecodeError: 'ascii' codec can't decode byte 0xef in position 1 Placing Unicode character in CSS content value Usage of unicode() and encode() functions in Python Python string to unicode UnicodeEncodeError: 'ascii' codec can't encode character u'\xa0' in position 20: ordinal not in range(128) Write a file in UTF-8 using FileWriter (Java)? Removing u in list Python - 'ascii' codec can't decode byte

Questions with varchar tag:

SQL Server date format yyyymmdd What does it mean when the size of a VARCHAR2 in Oracle is declared as 1 byte? Difference between VARCHAR and TEXT in MySQL PostgreSQL: ERROR: operator does not exist: integer = character varying Can I use VARCHAR as the PRIMARY KEY? Is the LIKE operator case-sensitive with MSSQL Server? How to convert Varchar to Double in sql? SQL Server : error converting data type varchar to numeric What is the MySQL VARCHAR max size? SQL Server Convert Varchar to Datetime Changing the maximum length of a varchar column? Finding rows that don't contain numeric data in Oracle Postgresql - change the size of a varchar column to lower length When increasing the size of VARCHAR column on a large table could there be any problems? MySQL VARCHAR size? How to get the size of a varchar[n] field in one SQL statement? Difference between text and varchar (character varying) MySQL: Large VARCHAR vs. TEXT? #1071 - Specified key was too long; max key length is 767 bytes How many characters in varchar(max) Is there a good reason I see VARCHAR(255) used so often (as opposed to another length)? What is the difference between CHARACTER VARYING and VARCHAR in PostgreSQL? oracle varchar to number What is the string length of a GUID? Using varchar(MAX) vs TEXT on SQL Server SQL Server Text type vs. varchar data type Equivalent of varchar(max) in MySQL? What is the difference between char, nchar, varchar, and nvarchar in SQL Server? What is the difference between varchar and nvarchar? Difference between BYTE and CHAR in column datatypes What are the main performance differences between varchar and nvarchar SQL Server data types?