Show right sided value, from a field

[Steggsy17]

Hi all,

Trying to replicate an excel calculation, within Cognos Report Studio.

For an integer field, the value is 0808586 210521

How can I extract just the value on the right hand side after the space?
I only want to see 210521 (it's a date)

Thank you

[MFGF]

Hi all,

Trying to replicate an excel calculation, within Cognos Report Studio.

For an integer field, the value is 0808586 210521

How can I extract just the value on the right hand side after the space?
I only want to see 210521 (it's a date)

Thank you

Hi,

You mentioned this is an integer field, but you can't have a space included in any sort of numeric data type (including integer). I'm guessing this is a character field?

Assuming this is the case, you can use a combination of functions to locate the space and extract the relevant characters following the space:

You can use the character_length() function to get the total number of characters in the field, you can use the position() function to find the position of the space, and you can use the substring() function to extract the relevant characters after the space. So, for example, if your field is called [item] in the report, the following expression in a query calculation would give you the characters after the space:

substring([item], position(' ', [item]) +1, character_length([item]) - position(' ', [item]))

Cheers!

MF.

[BigChris]

Are you always looking for the right most 6 characters in your string? If you're using SQL server there's a right() function that will do that for you

right([YourField],6)

[Steggsy17]

cheers guys, both of them logics work like a dream

they actually give me a solution for two different things, so talk about two birds...

Appreciate the time