Parse Name feild

[need2progm]

How do I parse a name field?
Example: Joe Smith in one field “Name”
I need a LastName field and a FirstName field.

Thanks in advance

 

[zhavic]

Try:

SELECT LEFT( [name], PATINDEX( '% %', [name] ) - 1 ) AS FirstName,
SUBSTRING( [name], PATINDEX( '% %', [name] ) + 1, LEN( [name] ) ) AS LastName
FROM your_table


You can also use CHARINDEX() for short strings
SELECT LEFT( [name], CHARINDEX( ' ', [name] ) - 1 ) AS FirstName,
SUBSTRING( [name], CHARINDEX( ' ', [name] ) + 1, LEN( [name] ) ) AS LastName
FROM your_table

Zhavic

---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.

 

[SQLSister]

Of course then you are going to have to deal with the special cases that don't just have one first name and one last name. SOme will have middle initials, some will have, M.D. or other suffixes, some will have prefixes. Some might have mutliple names that belong in one or the other field

Mary Jane Di Silva
has several possible correct versions for instance.

Mary could be the first name or Mary Jane

Di Silva is probably the last name, but your parsing will probably call it Silva.

There is no easy fox for first name, last name parsing.

 

[need2progm]

Thanks for the script. Out of 500 rows of data only 25 were dumped into a backup table.

I also tried using it with access but it does not like CHARINDEX

Stick with SQL Server.. That is what I tell my customers