SQL Server CHAR Data Type

原文地址：https://www.sqlservertutorial.net/sql-server-basics/sql-server-char/

Summary: in this tutorial, you will learn how to use the SQL Server CHAR data type to store the fixed-length, non-Unicode character strings in the database.

Overview of the SQL Server CHAR data type
If you want to store fixed length, non-Unicode string data, you use the SQL Server CHAR data type:

CHAR(n)
In this syntax, n specifies the string length which ranges from 1 to 8,000.

char(n)类型最大可以存储8000个字符；

 

Because n is optional, if don’t specify it in a data definition or variable declaration statement, its default value is 1.

You should use the CHAR data type only when the sizes of values in the column are fixed.

 

When you insert a string value into a CHAR column. If the length of the string value is less than the length specified in the column,

SQL Server will add trailing spaces to the string value to the length declared in the column.

However, when you select this string value, SQL Server removes the trailing spaces before returning it.

当你插入一个字符串到char类型列中时，如果这个字符串的长度小于列的定义长度，那么sqlserver会自动添加尾随空格，补充上这个长度；

然而，当你select 这个字符串时，sqlserver会自动将尾随空格去除后返回给你。

特例，在使用ADO控件append一条记录后，如果记录里有char类型列，而你给这列的长度又没有列长度长的话，那么在你post之后，引用这一列时，会看到

sqlserver自动给添加的尾随空格，所以这里在这个场景下，试图引用这样的列时，一定要注意这个尾随空格的情况。 切记切记！！

On the other hand, if you insert a value whose length exceeds the column length, SQL Server issues an error message.

Note that the ISO synonym for  CHAR is CHARACTER so you can use them interchangeably.

SQL Server CHAR data type example
The following statement creates a new table that contains a CHAR column:

CREATE TABLE test.sql_server_char (
    val CHAR(3)
);
Note that if you don’t have the test schema in the database, you can create it by using the following statement before creating the sql_server_char table:

CREATE SCHEMA test; 
GO
To insert a fixed-length character string into the CHAR column, you use the INSERT statement as follows:

INSERT INTO test.sql_server_char (val)
VALUES
    ('ABC');
The statement worked as expected.

The following statement attempts to insert a new character string whose length exceeds the column length:

INSERT INTO test.sql_server_char (val)
VALUES
    ('XYZ1');
SQL Server issued the following error:

String or binary data would be truncated.
The statement has been terminated.
The following statement inserts a single character into the  val column of the test.sql_server_char table:

INSERT INTO test.sql_server_char (val)
VALUES
    ('A');
In SQL Server, LEN function returns the number of characters in a specified column that excludes the trailing blanks and the DATALENGTH function returns the number of bytes.

See the following statement:

SELECT
    val,
    LEN(val) len,
    DATALENGTH(val) data_length
FROM
    sql_server_char;
sql server char
Even though the character ‘A’ is only one character, the number of bytes of the column is fixed which is three.

In this tutorial, you have learned how to use the SQL Server CHAR data type to store fixed-length, non-Unicode character strings in the database.