Padding Zero's 1

[KizMar]

Using SQL Query Analyzer:

How can I pad zero's in front of a varchar/str field to make the length 10 no matter how many characters are pulled in the query?

Ex: Pulling "U123", want it to display as "000000U123"
"LOCA01", want it to display as "0000LOCA01"

 

[clapag22]

replicate('0', 10 - len(fieldname)) + fieldname

CML

 

[KizMar]

PERFECT!!! Thank you!!!!

 

[MarkDicken]

Thankyou - It Helped me Also

REF: replicate('0', 10 - len(fieldname)) + fieldname

Regards

Mark Dicken, England, UK
(

http://www.MarkDicken.com)

 

[CanadianTechie]

With reference to clapag22's solution, I would make one more modification -- in current versions of MSSQL, concatenation of strings that contain a NULL value will evaluate to NULL. So in the example above, if fieldname is NULL, NULL will be returned. This could be rectified by wrapping the fieldname in COALESCE statements as:

replicate('0', 10 - len(COALESCE(fieldname,''))) + coalesce(fieldname,'')

The first COALESCE might not be necessary..off the top of my head, unsure of whether len(NULL) evaluates to 0 or NULL...the extra COALESCE certainly would not hurt...In this example, if fieldname contains a NULL value, 10 0's will be returned, which I believe is the desired outcome...

Pedro Cardoso
A.K.A CanadianTechie