The perils of not specifying a variable or parameter length
December 12th, 2008When declaring a variable or parameter as varchar or nvarchar, you might get into some nasty surprises if you don’t specify the number of characters in the data. If you are declaring a variable like DECLARE @string AS VARCHAR, you are going to end up with a string that only holds 1 character. That’s probably not what you intended, but rather DECLARE @string AS VARCHAR(50) or similar.
The behaviour is similar when using CAST and CONVERT. Then, the default is to limit the data to 30 characters.
Some examples of this:
DECLARE @string AS VARCHAR
SET @string = 'Lorem ipsum dolor sit amet, consectetur adipisicing elit'
SELECT
'Lorem ipsum dolor sit amet, consectetur adipisicing elit' AS [Original string],
LEN('Lorem ipsum dolor sit amet, consectetur adipisicing elit') AS [Length of original string],
@string AS [varchar],
CAST('Lorem ipsum dolor sit amet, consectetur adipisicing elit' AS VARCHAR) AS [Casted varchar],
LEN(CAST('Lorem ipsum dolor sit amet, consectetur adipisicing elit' AS VARCHAR)) AS [Length of casted varchar]
GO
DECLARE @string AS NVARCHAR
SET @string = N'Lorem ipsum dolor sit amet, consectetur adipisicing elit'
SELECT
N'Lorem ipsum dolor sit amet, consectetur adipisicing elit' AS [Original string],
LEN(N'Lorem ipsum dolor sit amet, consectetur adipisicing elit') AS [Length of original string],
@string AS [nvarchar],
CAST(N'Lorem ipsum dolor sit amet, consectetur adipisicing elit' AS NVARCHAR) AS [Casted nvarchar],
LEN(CAST(N'Lorem ipsum dolor sit amet, consectetur adipisicing elit' AS NVARCHAR)) AS [Length of casted nvarchar]Results:
Original string Length of original string varchar Casted varchar Length of casted varchar -------------------------------------------------------- ------------------------- ------- ------------------------------ ------------------------ Lorem ipsum dolor sit amet, consectetur adipisicing elit 56 L Lorem ipsum dolor sit amet, co 30 (1 row(s) affected) Original string Length of original string nvarchar Casted nvarchar Length of casted nvarchar -------------------------------------------------------- ------------------------- -------- ------------------------------ ------------------------- Lorem ipsum dolor sit amet, consectetur adipisicing elit 56 L Lorem ipsum dolor sit amet, co 30 (1 row(s) affected)