Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Get Middle Initial from a Name field-SQL Server 2008

Status
Not open for further replies.

rbcasper

Technical User
Jul 21, 2013
5
US
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
 
Hi,

How about this...

SQL:
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
 
Hi, thank you for your quick response. The code works perfect! Thank you very much.
rb
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top