SQL VARCHAR Data Type

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.

Example

#

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
Result:  5 records
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

Syntax of VARCHAR.

VARCHAR(number)

number -- optional, # of bytes (1 - 8,000). Default is 1.

More Examples

VARCHAR with CONVERT MONEY VALUE

ORDER
Id
OrderDate
OrderNumber
CustomerId
TotalAmount
Problem: List monthly sales for the year 2013 with properly formatted $ amounts.
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)
Result:  12 records
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.


You may also like



Guides


vsn 3.1