Adding zeros to number

[#1]

Hi,

Can you please help?

I have a field with a integer for example 0001234567 coming from a DB, and I have the same data field coming from  a excel spread sheet in the following format 1234567.

How can I add zeros to the left of the number coming form the excel sheet, or how do I take off zeros from the left data coming from the DB before loading the data so these two data fields can correspond to the same thing and be associated.

Some data coming from the Excel sheet in this field, are like 234567, then I would have to add 4 zeros to the begining of the number 0000234567 in order to be associated with the one coming from the DB. The one form the DB always come with a fixed amount of numbers, in this example 10 digits.

I look forward to hearing your suggestions.

Thks a lot.

Jorge

[#2 · Accepted solution]

If you wanted to add 0's you could try something like

repeat('0', (10 - len(Field))) & Field as NewField

Then NewField will have the zero padding to fill out length 10 and should unify your results.

[CELAMBARASAN]

Hi,

     To add zeros use

     Num(FieldName,'0000000000') as NumFieldName

     To remove zeros use

     Num(FieldName) as NumFieldName

Celambarasan

[Jason_Michaelides]

Do you really need to?  I would have though QV would recognise 0001234567 as the number 1234567 and load it as such.  It would therefore link to the 1234567 from the Excel spreadsheet.

If this is not happening you could try Num(IntegerFieldFromDatabase)  during the load to convert to a number.

If that doesn't work then there is a solution for removing leading zeros here.

Hope this helps,

Jason

[#5]

Thanks a lot bapperson, that worked. Thanks very much as well for those that participated.