Split field?

[rcolijn]

Hello fellow cognites!

I am trying to build a report which includes a field with firstnames from people, the customer only wants to see the first name in the field, and not the rest. So I am looking for the following:

Field contents: "Bob Larry Smith the 3rd" should show "John"
Field contents: "Bob Warren Rose" should show "Bob"
Field contents: "Bob Smith" should show "Bob"
Field contents: "Warren Angle James" should show "Warren"
Field contents: "James" should show "James"

Basically anything before the first space, or when there is only one word then that whole field.

Is there something like a function like First_Word([Model].[Subject].[Field])????

Any advice appreciated, oh I can do this in Framework manager too however would rather not.

Ronald
ps Cognos 8BI Report Studio MR1

[blom0344]

Something like:

Code Select Expand


substring(dataitem,1,(position(' ',[dataitem])))


You may need to adjust with  -1 somewhere to get the proper cut-off, but you'll get the idea..

[Gopinath]

The code Blom suggested will work fine, but add -1 to it to get the exact string without a white space

substring([Name],1,position(' ',[Name])-1)

[rcolijn]

Thanks fella's I'll give that a go, would this also work for fields without a space in it? so for instance where its only "Bob" or do I have to IF a rround this?

[rcolijn]

Found it out, I changed it to the following and it works great:

IF (position(' ',[Interface Files].[Employee].[FirstNames]) > 0 )
THEN (substring([Interface Files].[Employee].[FirstNames],1,position(' ',[Interface Files].[Employee].[FirstNames])-1))
ELSE ([Interface Files].[Employee].[FirstNames])

Ronald