How to add leading zero's and make sure all the values have equal number of digits?

[#1]

Hi,

I'm seeing all kinds of posts on how to remove leading zero's but I'm looking for a way to add leading zero's.

I've got a field with entries that have 6 or 7 digits. I need them all to have 7 digits and add a leading zero to the ones that have 6 digits

For example:

123456

1234567

Need to be transferred into

0123456

1234567

I've tried using the format command num(value,'#######') but that doesn't do the trick. I'm guessing it should also be something with text(value), but how to add that extra zero?

My table has about 4 mln records, so I guess I have to look for a fairly straightforward solution to keep it operateable...

Thanx!

Gjalt

[Miguel_Angel_Baeyens]

Hello Gjalt,

If those values don't have the leading zero in your data source, then you can add them doing something like the following in your load script:

If(Field < 1000000, '0' & Field, Field) AS FieldWithZeroes,

That should work. Anyway, you can use the Text() function to make sure you are representing those values as strings, and not as numeric values (where 1234 is equal to 00001234)

Text(If(Field < 1000000, '0' & Field, Field)) AS FieldWithZeroes,

Hope this helps.

[#3]

Hello Gjalt,

there are even more then one solution. The easiest in my opinion is:

IF (LEN(Inputfield) = 6, '0' & Inpufield, Inputfield) AS InputField;

Being more flexible: LEFT('0000000000', 10 - LEN(Inputfield)) & Inputfield

Regards, Roland

[#4 · Accepted solution]

Hi Gjalt,

you are very near to a solution.

Use

NUM(YourField,'0000000') as LeadingZeros

That´s all.

Good luck!

Rainer

[#5]

Thanks for the quick answers guys. The one by Rainer worked excellent and seems the easiest.

so close....!

[Miguel_Angel_Baeyens]

Based on what Roland suggests, and regardless the number of digits the code may have, the following will work

Repeat('0', 10 - Len(Field)) & Field AS Field,

This way you don't have to take care on how many digits the actual value has. I've set 10 as maximum number of digits a value can have.

I'd use something like this instead of the Num() formatting since it's still a number, and depending on your datamodel and the charts you want to draw, num 01234 and num 1234 will be the same, although they are not.

Hope this helps.

[#7]

Hi Miguel,

Thanx for the extra answer. Both yours and Rainers solution work for me, but always good to know several ways to do it!

Gjalt

[#8]

num(value,'0000000')

Suerte