Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Formatting a field with leading zeros 2

Status
Not open for further replies.

jms493

MIS
May 16, 2007
11
US
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
 
Code:
SELECT RIGHT('00000'+CAST(YourField as varchar(5)),5)


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

Code:
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
 
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
 
SQLsister I agree but I dont have access to the source file.......someone just wanted a quick query......might be a one time affair
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top