SQL Question (Help!) 2

[Hobbster]

I'm new to SQL, so please bear with me...

database = "users"
table = "info"
field = "name"

I have a situation where data in the "name" field is in the form of:
LastName, FirstName MiddleInitial.

I need to change it to the following form:
FirstName MiddleInitial. LastName

And then I need to update each row to fit the new form.

How can I do that in SQL?
I would really appreciate anyone's help or direction in finding out how to
do this.

Thanks,
Eric

 

[kathryn]

Sorry, but you have just discovered why you generally split out all parts of a person's name: Prefix/Title, First, Middle, Last, Suffix.

So my first suggestion is to modify your table to have three fields instead of one. Is that a possibility?

If not, you can do what you want but we will probably have to use code. Does EVERY record has three names in it (more to the point does every record have two commas?).

Let me know.
Kathryn

 

[Hobbster]

Unfortunately, I didn't design this database. It was created about a year ago in Filemaker Pro. I'm just the lucky guy who gets to modify it for use on the web!

At this point, anything is a possibility except for a loss of data. There are approximately 5400 records in this db. I've transferred all of the data from the existing FMpro db to SQL Server 7.

The SQL database has the appropriate 3 fields (NameFirst, NameLast, NameMI), but I need to find a way to fill those fields with the data from that old "Name" field.

Now, every record in that "Name" field is formatted like:
LastName, FirstName MiddleInitial.

My Primary goal in this is to seperate the last names from the first and middle names -- seperating middle names from first names is a tertiary goal.


- Eric

 

[kathryn]

OK, you have the fields ready, now you get to learn all the string manipulation functions.

Since you posted this to the Access forum, I am assuming that you have this table linked into an MS Access database. If that is incorrect, let me know. The answer won't be that much different.

What you need is an update query.

Create a new query in Access and add your Info table.

From the menu chose Query -> Update Query.

Add the NameLast field to the query grid.

On the Update To: line in the grid, under NameLast, enter the following:
Left([info]!name, instr([[info]![name],",&quot-1)

What this will do is to update the NameLast field to the left most characters of the info!name field. The left function takes two arguments, the string to return the characters from, and the number of characters. Here the number of characters is defined to be the number that is one less than the position of the first comma.

To give you an example, in the following example:

Left("Doe, John", Instr("Doe, John",",&quot-1)

Instr("Doe, John",",&quot will return the number 4, because the comma occurs as the fourth character. By subtracting one, we get 3, which is the number of leftmost characters we want.

Hope this all makes sense.
Kathryn

 

[Hobbster]

Kathryn,
It worked! Thank you very much for your help.

I followed your example to place the first name into the NameFirst column:

Right([info]!Name, int(len([info]!Name) - instr([info]!Name,",&quot-1))


- Eric