How to add zeros to a character type column ?

[NicolaTesla1]

Hi,

We use Report Studio 8.3
We need to format a column of type "character" in our list.

The column has 5 to 7 characters.

We want to format the column in order to ADD "zeros" when less than 7 characters.

For example a column with the value:
12345
would become
0012345

Is this possible and how ?

Thanks !

 

[blom0344]

Simple, use the character_length function to determine the length of the string. Depending on the outcome use concatenate function ('||') to add the zeroes:

CASE CHARACTER_LENGTH([SOMEFIELD])
WHEN 5 THEN '00'||[SOMEFIELD]
WHEN 6 THEN '0'|| [SOMEFIELD]
............
............
ELSE NULL
END

Ties Blom

 

[NicolaTesla1]

Thanks blom 0344 !

I will try it and let everyone know.

Just hope i wont have any strange error messages as for when i tried something else before for this same problem.

I used the function
cast([DataItem]; INTEGER)
or
cast([DataItem]; NUMBER)

When i validated the report there were no errors but when executing the report i got an error message. I noticed that when in the expression editor, there was a small red exclamation mark next to the name of the cast function.

Could that mean that because we are using packages against cubes coming from BW we cannot use the cast function ??

More information soon.

 

[blom0344]

Possibly, you should check if CASE can be used with Cube packages. I recall that you should If Then Else instead.

Ties Blom

 

[NicolaTesla1]

Hello to all,

Just for your information:

This is not possible for our team as for many functions are unable to us and have exclamation marks ...
this includes character_length, cast, extract etc ...

I managed to use the key column instead and i am now having what expected.

Good day to all !

 

[blom0344]

The difference in the use of functions lies in whether you are using a relational / dimensional package. Relational uses SQL functions, Dimensional MDX types..

Ties Blom