Adding leading zeros to int column

Hi there,

some of my imports needs by design for the importcounter not integers like 1, 2, 3, 4, 5,...
they expect varchar with leading zeros. Today I will show you how to add leading zeros
to your integers by a simple combination of cast() and right().

Let me explain the function:
RIGHT('0000000000000000000000'+cast([Itemno] as varchar (255)), (select cast(LEN(max(itemno)) as int) from [article])) as ImportID

First of all
'0000000000000000000000'+cast([Itemno] as varchar (255)
this adds leading zeros before the int field which we cast to varchar(255)
after that we use the right function to take the first X chars from the right side of string.
RIGHT('0000000000000000000000'+cast([Itemno] as varchar (255)), X)
but my imports need as input the a variable lenght addicted by the lenght of maximum int value,
so I replaced the X with select cast(LEN(max(itemno)) as int) from [article] this query returns for the demodata 5 because the max number used is 50000 and len of 50000 = 5.
This way i got my importers working.

Copyable Version:
-- Table with int values
SELECT    [Itemno] ImportID,
        [Itemname],
        [Price]
  FROM [article]

-- Table with leading zeros as varchar
SELECT    RIGHT('0000000000000000000000'+cast([Itemno] as varchar (255)),
        (select cast(LEN(max(itemno)) as int) from [article])) as ImportID,
        [Itemname],
        [Price]
  FROM [article]