The VARCHAR data type stores variable-length character strings.
Use VARCHAR when there is variability in the size of the data.
VARCHAR may hold up to 8,000 ASCII characters of data.
The word VARCHAR stands for varying character.
A table with 2 VARCHAR columns.
CREATE TABLE DemoTable
(
Id INT IDENTITY,
FullName VARCHAR(100),
Address VARCHAR(250)
);
GO
INSERT INTO DemoTable VALUES ('Harold Smith', '4939 Conifer Drive, Seattle, Washington');
INSERT INTO DemoTable VALUES ('Robert Johnson', '820 Olen Thomas Drive, Weinert, Texas');
INSERT INTO DemoTable VALUES ('Janice Lopez', '3052 Archwood Avenue, Cheyenne, Wyoming');
INSERT INTO DemoTable VALUES ('Kelly Wilson', '4251 Payne Street, Spring Lake, New Jersey');
INSERT INTO DemoTable VALUES ('Grace Taylor', NULL);
GO
SELECT * FROM DemoTable;
GO
DROP TABLE DemoTable;
GO
| Id | FullName | Address |
|---|---|---|
| 1 | Harold Smith | 4939 Conifer Drive, Seattle, Washington |
| 2 | Robert Johnson | 820 Olen Thomas Drive, Weinert, Texas |
| 3 | Janice Lopez | 3052 Archwood Avenue, Cheyenne, Wyoming |
| 4 | Kelly Wilson | 4251 Payne Street, Spring Lake, New Jersey |
| 5 | Grace Taylor | NULL |
Syntax of VARCHAR.
VARCHAR(number)
number -- optional, # of bytes (1 - 8,000). Default is 1.
| ORDER |
|---|
| Id |
| OrderDate |
| OrderNumber |
| CustomerId |
| TotalAmount |
SELECT MONTH(OrderDate) AS Month,
'$' + CONVERT(VARCHAR(9), SUM(CAST(TotalAmount AS MONEY)), 3) AS 'Total Sales'
FROM [Order]
WHERE YEAR(OrderDate) = 2013
GROUP BY MONTH(OrderDate)
ORDER BY MONTH(OrderDate)
| Month | Total Sales |
|---|---|
| 1 | $ 66,692.80 |
| 2 | $ 41,207.20 |
| 3 | $ 39,979.90 |
| 4 | $ 55,699.39 |
| 5 | $ 56,823.70 |
 |
|
This demonstrates a cast to a variable sized VARCHAR(9) type.
However, if sales were to exceed $100,000, an overflow error would occur.
