To add zeros in front of fields in order them to be with a length of 6

[nevilledhamsiri]

Hi Members,

Please help me to achieve the said out put. As for my data, I need both EPF Number & Emp fields to be with 6 digit lengths & as long as the length of the  record remains less than 6 digits, I need zeros to make it 6 digit lengths.

Eg   3988 to be appeared as 003988 ,once data are loaded. The excel carries this length but once loaded it turns to be a number resulting all zeros getting dropped.

Thanks

Neville

[dplr-rn]

change your load script to

LOAD BR_Code, Month, EPF_NO, ''&EMP_NO as EMP_NO, [Employee_ Name], PAID

basically force engine to recognize that column as string

[nevilledhamsiri]

Does this add zeros in front of emp_no to appear it with 6 digit lengths?

Could you add more details because I have no idea how to make it

[vikramv · Accepted solution]

Use repeat function like...

Repeat('0', 6-len(EPF)) & EPF   as  EPF

Thanks.

Vikky

[nevilledhamsiri]

Thanks Vikky,

amazing answer. This is what I needed.

Thanks a lot.

Neville

[Vegar]

That's a neat one @vikramv 

I usually do like this

Right('000000'& EPF, 6)  as  EPF,

Or like this if I know that EFT is an integer.

num(EPF, '000000') as EPF,

[vikramv]

Welcome !

[nevilledhamsiri]

Thanks 

Vegar

Fine answer too.

[jduenyas]

Right('000000'+EPF,6) AS EPF

[nevilledhamsiri]

Thanks Jduenyas

Hlpful answer too