Get Middle Initial from a Name field-SQL Server 2008

[rbcasper]

Hello,
I need to extract the middle initial from a name field that contains the last name, comma, and the middle name or initial. There is no space after the comma and, in some cases, there is a space in the last name.
Example data:
Smith,Jane Ron
Dow,John L
Ali Welsh,Mary Jane

The result I am looking for is to capture the first letter of the middle name. In this data example, I would need to capture the following on a separate column:

R
L
J

Any help will be greatly appreciated.

rb

 

[paulkeys]

Hi,

How about this...

create table #temp(Name varchar(50))
insert #temp(Name) values ('Smith,Jane Ron'),('Dow,John L'),('Ali Welsh,Mary Jane')
select
Name,
SUBSTRING(Reverse(Name),charindex(' ',REVERSE(Name),1)-1,1)
from #temp
drop table #temp

 

[rbcasper]

Hi, thank you for your quick response. The code works perfect! Thank you very much.
rb