CHAR is a fixed-sized character data type.
Use this type when values are consistent in length.
The max CHAR size is 8,000, storing up to 8,000 ASCII characters.
The word CHAR stands for character.
A table with 2 CHAR columns.
CREATE TABLE DemoTable
(
Id INT IDENTITY,
FirstName VARCHAR(100),
LastName VARCHAR(100),
MiddleInitial CHAR,
Gender CHAR(10)
);
GO
INSERT INTO DemoTable VALUES ('Harold', 'Smith', 'A', 'Male');
INSERT INTO DemoTable VALUES ('Robert', 'Johnson', 'J', 'Male');
INSERT INTO DemoTable VALUES ('Janice', 'Lopez', 'B', 'Female');
INSERT INTO DemoTable VALUES ('Kelly', 'Wilson', 'H', 'Female');
INSERT INTO DemoTable VALUES ('Grace', 'Taylor', NULL, 'Female');
GO
SELECT * FROM DemoTable;
GO
DROP TABLE DemoTable;
GO
| Id | FirstName | LastName | MiddleInitial | Gender |
|---|---|---|---|---|
| 1 | Harold | Smith | A | Male |
| 2 | Robert | Johnson | J | Male |
| 3 | Janice | Lopez | B | Female |
| 4 | Kelly | Wilson | H | Female |
| 5 | Grace | Taylor | NULL | Female |
Note: If a value exceeds the allocated CHAR size, an error will occur.
Syntax of CHAR.
CHAR(number)
number -- optional, # of ASCII characters (1 - 8,000). Default is 1.
| ORDER |
|---|
| Id |
| OrderDate |
| OrderNumber |
| CustomerId |
| TotalAmount |
SELECT MONTH(OrderDate) AS Month,
'$' + CONVERT(CHAR(9), SUM(CAST(TotalAmount AS MONEY)), 3) AS 'Total Sales'
FROM [Order]
WHERE YEAR(OrderDate) = 2013
GROUP BY MONTH(OrderDate)
ORDER BY MONTH(OrderDate)
This demonstrates a cast to a fixed length CHAR(9) type.
If sales exceed $100,000, an overflow error will occur.
| Month | Total Sales |
|---|---|
| 1 | $ 66,692.80 |
| 2 | $ 41,207.20 |
| 3 | $ 39,979.90 |
| 4 | $ 55,699.39 |
| 5 | $ 56,823.70 |
 |
|
