If you are unable to create a new account, please email bspsoftware@techdata.com

Author Topic: Show right sided value, from a field  (Read 432 times)

Offline Steggsy17

  • Associate
  • **
  • Join Date: Jun 2022
  • Posts: 2
  • Forum Citizenship: +0/-0
Show right sided value, from a field
« on: 27 Jun 2022 10:49:59 am »
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

Offline MFGF

  • Never knowingly correct
  • Super Moderator
  • Statesman
  • ******
  • Join Date: Jul 2005
  • Posts: 11,524
  • Forum Citizenship: +671/-10
  • Cognos Software Muppet
Re: Show right sided value, from a field
« Reply #1 on: 27 Jun 2022 11:50:31 am »
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.
Meep!

Offline BigChris

  • Statesman
  • ******
  • Join Date: Apr 2013
  • Posts: 1,266
  • Forum Citizenship: +91/-0
Re: Show right sided value, from a field
« Reply #2 on: 27 Jun 2022 04:23:43 pm »
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)

Offline Steggsy17

  • Associate
  • **
  • Join Date: Jun 2022
  • Posts: 2
  • Forum Citizenship: +0/-0
Re: Show right sided value, from a field
« Reply #3 on: 28 Jun 2022 03:21:14 am »
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