Add leading zeros to number/string

[Dampa]

Dears,

I'm trying to add dynamically some leading zeros to a field that can contains numbers or numbers and text. Examples below:

1234 > 0000001234
123   > 0000000123
123ABC > 0000123ABC

Is there any way to do so? In Excel I'm playing with LEN and REPT formula, but in Cognos I'm stacked.

Unfortunately formatting the field with a defined pattern doesn't work in my case.

Thanks,
D

[MFGF]

Dears,

I'm trying to add dynamically some leading zeros to a field that can contains numbers or numbers and text. Examples below:

1234 > 0000001234
123   > 0000000123
123ABC > 0000123ABC

Is there any way to do so? In Excel I'm playing with LEN and REPT formula, but in Cognos I'm stacked.

Unfortunately formatting the field with a defined pattern doesn't work in my case.

Thanks,
D

If the original field contained just numbers, it would be easier. In that case, I would cast the field to an integer data type and numerically add it to the value 10000000000

eg

10000000000 + cast( [your field], integer )

this would give you numeric results such as 10000001234

You could then convert these back to character values and use the substring function to extract from the second to the eleventh position, eg

substring( cast( [your calculated item above], varchar(11) ), 2,11)

This would then give you values such as 0000001234

The downside is this only works with values that originally contain only numeric characters. You could possibly deal with the others by extracting the alphabetic characters first, then using this approach to zero-pad the numeric part, then append the alphabetic characters back again before the final substring. That gets a bit messy, though - not sure if it would be more work than it's worth.

Cheers!

MF.

[cognostechie]

If the original field contained just numbers, it would be easier. In that case, I would cast the field to an integer data type and numerically add it to the value 10000000000

eg

10000000000 + cast( [your field], integer )

this would give you numeric results such as 10000001234

You could then convert these back to character values and use the substring function to extract from the second to the eleventh position, eg

substring( cast( [your calculated item above], varchar(11) ), 2,11)

This would then give you values such as 0000001234

The downside is this only works with values that originally contain only numeric characters. You could possibly deal with the others by extracting the alphabetic characters first, then using this approach to zero-pad the numeric part, then append the alphabetic characters back again before the final substring. That gets a bit messy, though - not sure if it would be more work than it's worth.

Cheers!

MF.

Brilliant !

@Dampa - Maybe use the len function to check the length of the data and then use a Case statement to Check the length and then add the 100.. value. It looks like you want to add leading zeroes to make the length of the value 10.

[dougp]

Assuming you're dealing with strings, here are some examples using the Country query item from the samples data to make the string 15 characters long by padding the left with zeros.

LPAD function (Oracle, MySQL):

Code Select Expand

lpad([Sales (query)].[Branch].[Country], 15, '0')

LEN function (SQL Server):

Code Select Expand

substring('000000000000000', 1, 15-len([Sales (query)].[Branch].[Country])) || [Sales (query)].[Branch].[Country]

REVERSE function (SQL Server):

Code Select Expand

reverse(substring(reverse([Sales (query)].[Branch].[Country]) || '000000000000000', 1, 15))

REPLICATE and LEN functions (SQL Server):

Code Select Expand

replicate('0', 15-len([Sales (query)].[Branch].[Country]) || [Sales (query)].[Branch].[Country]

REPEAT and LENGTH functions (MySQL):

Code Select Expand

repeat('0', 15-length([Sales (query)].[Branch].[Country]) || [Sales (query)].[Branch].[Country]

I'm sure there are many other ways to do this.

[Dampa]

Thanks all for the useful inputs and thanks dougp for providing a code similar to what I'm doing in Excel.
In the meantime I solved with a case statement but I will try one of them for the final solution.