The char and varchar data types store data composed of:
char or varchar data can be a single character, or a string of up to 8,000 characters.
Character constants must be enclosed in single quotation marks (‘) or double quotation marks (“). Enclosing a character constant in single quotation marks is always allowed and is the recommended practice. Enclosing a character constant in double quotation marks is sometimes not allowed when the QUOTED IDENTIFIER option is set 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 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, Microsoft® 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.
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 OFF when the column was created, any trailing blanks are trimmed from character values stored in the column. If ANSI_PADDING was set ON when the column was created, trailing blanks are not trimmed.
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 trimmed.
The CHAR function can be used to convert an integer code to an ASCII character. This is useful when trying to specify control characters, such as a carriage return or line feed. Use CHAR(13) and CHAR(10) to put a new line and carriage return in a character string:
PRINT 'First line.' + CHAR(13) + CHAR(10) + 'Second line.'
The way the bit patterns stored in the bytes of a character string are interpreted is based on the Microsoft SQL Server code page specified during Setup. A char or varchar object can contain any character in the SQL Server code page. For more information, see Code Pages and Sort Orders.
DB-Library applications and applications using the SQL Server ODBC drivers from SQL Server version 6.5 or earlier support only up to 255 bytes of character data. If these applications attempt to retrieve SQL Server version 7.0 character parameters or result set columns containing more than 255 bytes of data, the character data is truncated at 255 bytes.
| Character Data | Data Types |
| sp_dbcmptlevel | SET ANSI_PADDING |