Swap words from a string.

[tigorin]

Hi. I have some data that I want to display from a table in the following manner: I have two columns, the first name and last name. Let's say I have a first name + last name John Martin Doe. What's a SQL script that can only display (not modify the name in the actual table) the name as Martin John Doe. Thanks.

[orange]

I suggest you start here.

[tigorin]

I did, that's where I come from. Maybe there's something I overlooked and I can't figure it out.

[orange]

What is your table name?
Post your sql.

[tigorin]

I have found a workaround for individual names:
SELECT REPLACE(Info.Name,"John Martin Doe", "Martin John Doe") AS Name
FROM Info;

This returns Martin John Doe, as I wanted. But I need a generic formula that I can use to automatically switch data like that.

[Xipooo]

You just wrote it.

SELECT Replace(Info.name, [Name], [OtherName])

But I'm not sure why you aren't just doing a SELECT on the OtherName and renaming the field name.

SELECT [OtherName] As Name

[tigorin]

And what if I have 1000 names to modify like that? Do I need to take each and every one by hand?

[rpeare]

how do you know which name is supposed to go first? In your example you don't say which set of text is stored in which field.

So is the first name John Martin and the surname Doe
or is the first name John and the surname Martin Doe?

There are all kinds of things you can do to manipulate a string or strings but you haven't given enough information to help in determining what that should be.

If your string is actually a single field (which seems to be the case based on your formula) what are the parameters of how that data is stored?

I'm assuming this is supposed to be FIRST MIDDLE LAST based on your example so in the case of a person where there was no middle name you'd have FIRST LAST and you'd need to account for that but really your example isn't clear enough to determine that.

[tigorin]

Ok, leaving aside my above example formula(I wrote it like that only to simplify), I have two columns in a table. One is Name and the other is Surname. In Name I have the field John Martin and in Surname, Doe. The single result that I want to be displayed by using a formula in SQL is Martin John Doe. If there's any more info needed, please say.

[June7]

And you want this for every single record? Does every record have two parts in the Name field? You need to swap the parts for every record? Why is this needed?

BTW, Name is a reserved word. Should not use reserved words as field names.

[tigorin]

Yes, I want for all records, not every record have 2 names and yes, I need to swap for every record.

[rpeare]

Ok so your rule is:

If the first name is two words you want the second word to appear first, otherwise just use the first name?

Assuming for the sake of argument that your data entry is 100% reliable and that there is, at most, one space in the name appearing in the firstname field you could have something like

IIf(InStr([firstname]," ")=0,[firstname],Right([firstname],Len([firstname])-InStr([firstname]," ")) & " " & Trim(Left([firstname],InStr([firstname]," ")))) & " " & [lastname]

[tigorin]

Yes, that's the rule. That code doesn't work for me, and I don't know if that's VBA or not, but I don't need VBA, only SQL script.

[rpeare]

that's not vba, that's a formula you can plug into a query, it assumes you have two fields on your table named FIRSTNAME and LASTNAME, if you don't then modify it to your table structure.