First name and last name position needs to change

[cognos74]

Hi,

I have a Name data item in my query which includes with First name and last name. (Ex: David Brian)
How to change the first name and last name position in report studio query.

Ex: David Brian  needs to be changed to Brian David.

[bdbits]

If at all possible, and the first/last name are separated in the source, have them stored separately in the data warehouse.

Splitting names after the fact leads to a lot of problems. Consider the following list of names.

Richard Adams
John Q. Public
William Smith, Jr.
Hillary Rodham Clinton
William Henry "Bill" Gates III

Ignoring Cognos or any other kind of expressions for the moment... how would you logically split all of the above correctly?

[Grayson_Basil]

I would start by using instr to look for the 1st space, then create your data items, but with the middle name example from above, there is nothing i can image that would work