How to cut a string up to the space?

[Todd129]

Hi gurus:

I have a data item with string data that looks like: "firstname lastname"

How do I write a function at the report studio level to cut the string at the end of firstame, so that the data item now just looks like "firstname"

Many thanks!

Todd

[adik]

create a data item with the following formula: substring([Name], 1, position(' ', [Name]) - 1)

[Todd129]

Thank you so much this worked perfectly  8)

[Todd129]

How is it possible to start substring from the second space character, not the first. See data example:

[DATA ITEM] = 'Billy Joe Bob'

For this guy, I want to cut the string at the second space, not the first. Any ideas?

All the best, Todd

[adik]

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

[shradha]

I want to substring Name in three names
[name] = Fname Mname Lname
Now, i want to create 3 columns out of it
like
[name1] = fname
[name2] = Mname
[name3] = Lname

Any help would be appreciated. My DB doesn't supposrt most of the functions. Subsrting is supported.

TIA

[CognosPaul]

What database are you using? substring alone won't be sufficient, but most databases have a rich string function set.

[shradha]

We are using IBM optim archival solution DB. The function set is very limited.

[bus_pass_man]

If you have a fairly newish CA, if you know that you want to create two substrings based on the last occurrence of a space then use Occurrences_regex to determine the number of occurrences. 

Use that result in position_regex.

Put that into the starting point parameter of substring.

Except that can't handle a surname which itself has a space in it.  :(