How do I preserve leading zeros for varchar field?

Question

When saving a number with leading zeros into a string variable or column, leading zeros are truncated.

When I run this query:

declare @varchar as nvarchar(50)
set @varchar = 011
print @varchar

The result I get is 11.

Is there any way to get 011?

Asked2021-05-17bysandhun p.s

Answer

In your code sample, 011 is a numeric, not a string. SQL Server simplifies 011 to 11 it's numeric equivalent, then saves 11 as the string you see in your @varchar variable.

If you want the number to be treated as a string, and not a numeric, simply wrap it in quotes. Additionally, since your variable is unicode nvarchar and not simply varchar, you should prefix the quoted string with an N.

This will print the string, preserving leading zeros, using nvarchar as you do above:

DECLARE @nvarchar AS nvarchar(50);
SET @nvarchar = N'011';
PRINT @nvarchar;

But the same holds true with varchar as well:

DECLARE @varchar AS varchar(50);
SET @varchar = '011';
PRINT @varchar;

Answered2021-05-17byAndy Mallon