SyntaxFix

[sql] Difference between BYTE and CHAR in column datatypes

In Oracle, what is the difference between :

CREATE TABLE CLIENT
(
 NAME VARCHAR2(11 BYTE),
 ID_CLIENT NUMBER
)

and

CREATE TABLE CLIENT
(
 NAME VARCHAR2(11 CHAR), -- or even VARCHAR2(11)
 ID_CLIENT NUMBER
)

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.

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).

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

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).

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.

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).

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

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

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).

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).

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.

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

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.

Source: Stackoverflow.com

Examples related to sql

• 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

Examples related to oracle

• 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?

Examples related to unicode

• 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

Examples related to varchar

• 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

Tags

Libz Idoc Ruby-on-rails-2 Quantum-computing Minidom Vpython Nszombieenabled Typetraits Radcombobox Datatemplate Rebasing Qtwebkit Stdout Google-latitude Key-storage Browser-cache Flowplayer Tor Servletconfig Tchecklistbox Network-monitoring Upx Launching Yahoo-api Tivoli-identity-manager Unobtrusive-javascript Debug-backtrace Computer-science-theory Xml-import Linux-distro Rooted-device Imessagefilter Pseudo-streaming Cgimagemaskcreate Taint-checking Edt Mse Pygame Undefined-index Safe-mode Gnash Game-center Data-mining Staledataexception Roguelike Staticfilehandler Openstep Asynchronous-wcf-call Junit Nedmalloc Static-class Getdistance Lostfocus Premailer Kxml Jinput Klocwork Settimeout Commission-junction Elementhost Gmail-contextual-gadgets Entitlements Asp.net-2.0 Affiliates Mks Extreme-programming Mysqlpp Json-c Seaside Corrupt-data Sp-help-operator Time-trial Rendercontrol Setting Webcron Websitespark Indexed-image Gregorian-calendar Rda Ntl Google-groups Photologue Type-safety Principles Hasownproperty Library-path Wss Time-select Hresult Cclayer Vp8 Rogue-wave Rack-middleware Parrot Flood-fill Objective-c-blocks C#-to-f# Gitorious Adblock Jfreereport