SQL Newbee; Need to Separate First Name MI Last out of 1 column 1

[dddd]

I have a table that has a column called 'Name' in name I have the full names of individuals. (EX. John W. Smith) I need to separate the name into three separate columns. One last thing is that sometimes I have a middle initial and sometime I do not! Help please. I have gone in the direction of using 'Like' but I think I am going up the wrong tree.

 

[jnicho02]

Name processing can get complicated, but you can do something simple by looking for the spaces with a charindex. To get the surname you could work backwards through the string by reversing it.

This bit of code
[tt]declare @name varchar(15)
select @name = "John W. Wayne"

select @name,
charindex(" ",@name),
substring(@name,1,charindex(" ",@name)),
charindex(" ",reverse(@name)),
reverse(substring(reverse(@name),1,charindex(" ",reverse(@name))-1))[/tt]

produces a result of
[tt]
--------------- ----------- --------------- ----------- ---------------
John W. Wayne 5 John 6 Wayne

(1 row(s) affected)[/tt]


This by no means is the full answer, but may get you started. Problems come when there is no space between the initial and the surname, e.g. "John W.Wayne".