CLOSED as requirement has changed Extra spaces and trimming a field

[bpothier]

I have a field called COMMON_NAME that is padded with spaces after the name. I am trying to trim the spaces away so I can then remove the 4 rightmost characters and I can;t figure it out.

example
[THE COMPANY - DC3                                              ]

Would like
[THE COMPANY]

thanks in advance

Bret

ADDED - even better woudl be athe ability to remove everythign after a hyphen

[MFGF]

I have a field called COMMON_NAME that is padded with spaces after the name. I am trying to trim the spaces away so I can then remove the 4 rightmost characters and I can;t figure it out.

example
[THE COMPANY - DC3                                              ]

Would like
[THE COMPANY]

thanks in advance

Bret

ADDED - even better woudl be athe ability to remove everythign after a hyphen

Hi,

Try creating a query calculation with the following expression:

substring([COMMON_NAME],1,position('-',[COMMON_NAME]) - 2)

Cheers!

MF.

[bpothier]

HI there and thanks for the help,

So here are the steps I followed

In framework manager, I went the the Query Subject, created a new Query Item called UNIQUE_COMMON_NAME and in the expression definition I entered

substring([CRO_EQPT_SUPPLIES_TB].[COMMON_NAME],1,position('-',[CRO_EQPT_SUPPLIES_TB].[COMMON_NAME]) - 2)

It returned an error that says CCLUnknownError and that is it.

[bpothier]

it works if I use that statement in the expression definition of the data item in Report Studio but not in the expression definition section for the field in Framework manager