SQL NVARCHAR Data Type

The NVARCHAR data type stores variable-length character strings.

Use NVARCHAR when there is variability in the length of the data.

NVARCHAR may hold up to 4,000 Unicode characters of data.

The word NVARCHAR stands for national varying character.

Example

#

A table with 2 NVARCHAR columns.

CREATE TABLE DemoTable  
( 
  Id INT IDENTITY, 
  FullName NVARCHAR(100),
  Address NVARCHAR(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 NVARCHAR.

NVARCHAR(number)

number -- optional, # of UNICODE characters (1 - 4,000). Default is 1.

More Examples

NVARCHAR 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(NVARCHAR(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 NVARCHAR(9) type.
However, if sales were to exceed $100,000, an overflow error would occur.


You may also like



Guides


vsn 3.1