Convert Int to text in SQL Server: how to display preceding zero '0'?

Question

I have an int column that I need to convert it into text. I tried to convert it into varchar but it did not display the preceding 0 when I tried to run the report thru batch file or export it to Excel,.

SELECT 
    CAST(a.Value AS VARCHAR(20)) AS Value
FROM 
    dbo.HPatientIdentifiers a
JOIN 
    HPatientVisit b ON a.Patient_oid = b.Patient_oid
WHERE 
    b.VisitTypeCode IN ('IP', 'EOP')
    AND a.IsVersioned = 0
    AND a.TYpe = 'MR'
    AND b.VisitEndDateTime >= DATEADD(dd, -1, GETDATE())
ORDER BY  
     a.Value

OUTPUT:

Value
------
4824
5663
10276
10459

Expected result:

Value
-------
004824
005663
010276
010459

Please help.

http://stackoverflow.com/questions/16760900/pad-a-string-with-leading-zeros-so-its-3-characters-long-in-sql-server-2008 — Rachel Ambler, May 16 '17 at 16:55
Yes, the value is 6 characters (int) but when I exported to excel it does not show me the preceding zeros. — Lina, May 16 '17 at 17:05

score 1 · Answer 1 · answered May 16 '17 at 16:55

using right() and concatenating 0s to the casted varchar() column:

SELECT right('000000'+CAST(a.Value as varchar(20)),6) AS Value

FROM dbo.HPatientIdentifiers a
JOIN HPatientVisit b
ON a.Patient_oid=b.Patient_oid

WHERE b.VisitTypeCode IN ('IP','EOP')
AND a.IsVersioned=0
AND a.TYpe='MR'
AND b.VisitEndDateTime >= DATEADD(dd,-1, GETDATE())

ORDER BY  a.Value

Convert Int to text in SQL Server: how to display preceding zero '0'?

1 Answers1