Using char and varchar Data
The char and varchar data types store data composed of the following:
- Uppercase and lowercase characters such as a, b, and C.
- Numerals such as 1, 2, and 3.
- Special characters such as the "at" sign (@), ampersand (&), and exclamation point (!).
The char or varchar data can be a single character, or a string with a maximum of 8,000 characters for char data, and up to 2^31 characters for varchar data. The varchar data types can take two forms. varchar data can be of a specified maximum length of characters, for example, varchar(6) indicates that this data type can store a maximum of six characters; or it can be of the form varchar(max), which increases the maximum number of characters that can be stored by this data type to 2^31. For more information about varchar(max), see Using Large-Value Data Types.
Each char and varchar data value has a collation. Collations define attributes such as the bit patterns used to represent each character, comparison rules, and sensitivity to case or accenting. Each database has a default collation. When a column is defined, or a constant specified, they are assigned the default collation of the database unless you assign a specific collation using the COLLATE clause. When two char or varchar values having different collations are combined or compared, collation precedence rules determine which collation is used for the operation.
Character constants must be enclosed in single quotation marks (') or double quotation marks ("). Enclosing a character constant in single quotation marks is recommended. Enclosing a character constant in double quotation marks is sometimes not allowed when the QUOTED IDENTIFIER option is set to ON.
This Transact-SQL example sets a character variable to a value:
DECLARE @MyCharVar CHAR(25)
SET @MyCharVar = 'Ricardo Adocicados'
When using single quotation marks to delimit a character constant that contains an embedded single quotation mark, use two single quotation marks to represent the embedded single quotation mark. For example:
SET @MyCharVar = 'O''Leary'
If the data to be stored is longer than the number of characters allowed, the data is truncated. For example, if a column is defined as char(10) and the value "This is a really long character string" is stored into the column, SQL Server truncates the character string to "This is a ".
The char data type is a fixed-length data type when the NOT NULL clause is specified. If a value shorter than the length of the column is inserted into a char NOT NULL column, the value is right-padded with blanks to the size of the column. For example, if a column is defined as char(10) and the data to be stored is "music", SQL Server stores this data as "music_____", where "_" indicates a blank space.
If ANSI_PADDING is ON when a char NULL column is created, it behaves the same as a char NOT NULL column: values are right-padded to the size of the column. If ANSI_PADDING is OFF when a char NULL column is created, it behaves like a varchar column with ANSI_PADDING set OFF: trailing blanks are truncated.
The varchar data type is a variable-length data type. Values shorter than the size of the column are not right-padded to the size of the column. If the ANSI_PADDING option was set to OFF when the column was created, any trailing blanks are truncated from character values stored in the column. If ANSI_PADDING was set ON when the column was created, trailing blanks are not truncated.
The way the bit patterns stored in the bytes of a character string are interpreted is based on the Microsoft SQL Server 2005 code page specified during Setup. A char or varchar object can contain any character in the SQL Server code page. .
Applications using the SQL Server ODBC drivers from SQL Server version 6.5 or earlier support only a maximum of 255 bytes of character data. If these applications attempt to retrieve character parameters of SQL Server version 7.0 or later, or result set columns containing more than 255 bytes of data, the character data is truncated at 255 bytes.
See Also
Other Resources
Data Types (Transact-SQL)
char and varchar (Transact-SQL)
Collation Precedence (Transact-SQL)
SET ANSI_PADDING (Transact-SQL)
sp_dbcmptlevel (Transact-SQL)