Formatting a field with leading zeros 2

[jms493]

I am creating a query and want to bring back 2 fields to have 5 places.
field 1
123 I need to to be 00123

same with field 2

 

[bborissov]

SELECT RIGHT('00000'+CAST(YourField as varchar(5)),5)

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[AlexCuse]

like this

select right('00000' + cast(fld1 as varchar(5)), 5) as fld1pad
, right('00000' + cast(fld2 as varchar(5)), 5) as fld2pad
from leTable

Make sure you are treating your result as VARCHAR, as integers will not retain any padding.

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[SQLSister]

If these numbers should always have the leading zeros, it is better to store them that way in the database using a varchar instead of a numeric datatype. If you must select them often, it is not very efficient to have to cast them and then add the zeros and then cut back to the correct length every time you refer to this field. This type of operation should be done at the point where the record is entered so that it is only done once.

Questions about posting. See faq183-874

 

[jms493]

SQLsister I agree but I dont have access to the source file.......someone just wanted a quick query......might be a one time affair