CASE when is removing ZERO'S from a VARCHAR(3) 1

[SQLRory]

When creating a CASE when command in a select statement it is cutting what was originally '079' to an output of '79'. I have tried to CAST the CASE as an INT(3) to avoid the problem but is still removing the zero's before numbers. Do you know why this would do this? I have also tried CAST to a VARCHAR(3) which the original data type.

Here is the script....

SELECT DISTINCT
TRAINEE.TRAINEEID as 'Trainee ID',
TRAINEEPOT.SERIALNUMBER as 'Learner Ref',
TRAINEEPOT.POT as 'POT',
TRAINEE.LASTNAME as 'Surname',
TRAINEE.FIRSTNAME as 'First Name(s)',
TRAINEE.DOB as DOB,
datediff("yy",TRAINEE.DOB,TRAINEEPOT.STARTDATE) as 'Age at Start',
TRAINEE.GENDER as 'Gender',
EthLookup.LONGSTRING AS 'Ethnicity',
DisLookup.LONGSTRING as 'Disabled?',
TRAINEEPOT.STARTDATE as 'Client Start Date',
TRAINEEPOT.TERMINATIONDATE as 'Client Leave Date',
CONTRACTOR.DESCRIPTION as 'Contractor',
CONTRACTORDelivery.DESCRIPTION as 'Centre',
CASE WHEN TRAINEEPOT.ESFLOCALPROJECTNUMBER IS NULL OR TRAINEEPOT.ESFLOCALPROJECTNUMBER = '' THEN 123
ELSE CAST (TRAINEEPOT.ESFLOCALPROJECTNUMBER AS VARCHAR(3))
END as 'Project No',
TRAINEEPOT.DELIVERYPOSTCODE as 'Delivery Postcode',
TRAINEEDETAILS.NVQREF as ' Qualification Code',

MaytasFunc.maytasro.ckLSCTitleType(TRAINEEDETAILS.NVQREF,'title') AS 'Qualification Title',
MaytasFunc.maytasro.ckLSCTitleType(TRAINEEDETAILS.NVQREF,'type') AS 'Qualification Type'

FROM
Maytas3.TRAINEE TRAINEE
INNER JOIN Maytas3.TRAINEEPOT TRAINEEPOT ON
TRAINEE.traineeid = TRAINEEPOT.traineeid
LEFT JOIN Maytas3.TRAINEEDETAILS TRAINEEDETAILS ON
TRAINEEPOT.traineeid = TRAINEEDETAILS.traineeid
AND TRAINEEPOT.pot = TRAINEEDETAILS.pot
LEFT JOIN Maytas3.TRDELIVERYCONTRACTOR TRDELIVERYCONTRACTOR ON
TRAINEEPOT.traineeid = TRDELIVERYCONTRACTOR.traineeid
AND TRAINEEPOT.pot = TRDELIVERYCONTRACTOR.pot
LEFT JOIN Maytas3.CONTRACTOR CONTRACTOR ON
TRAINEEPOT.contractorid = CONTRACTOR.contractorid
LEFT JOIN Maytas3.CONTRACTOR CONTRACTORDelivery ON
TRDELIVERYCONTRACTOR.contractorid = CONTRACTORDelivery.contractorid
LEFT JOIN Maytas3.Lookup EthLookup ON
EthLookup.TABLENAME = 'TRAINEE' AND
EthLookup.FIELDNAME = 'ETHNICGROUP' AND
EthLookup.SHORTSTRING = TRAINEE.ethnicgroup
LEFT JOIN Maytas3.Lookup DisLookup ON
DisLookup.TABLENAME = 'TRAINEEDETAILS' AND
DisLookup.FIELDNAME = 'DISABLED' AND
DisLookup.SHORTSTRING = TRAINEEDETAILS.disabled
WHERE
CONTRACTOR.DESCRIPTION LIKE '%Batch%WS%'

 

[RiverGuy]

Where you have THEN 123, make it say THEN '123'