Portland Access User Group

Portland Access User Group Conference

September 28-30, 2019

The PAUG Database Designer International conference brings together a wide range of Access developers, consultants, power users and Access enthusiasts. This marks the 21st anniversary of the conference. We will once again be returning to the peaceful and natural surroundings of the Conference Center at Silver Falls State Park, which lends itself to a climate that fosters learning, creativity, and socializing.

Thursday, August 30, 2012

Parse First, Middle, and Last Name in T-SQL

I'm not a SQL Server T-SQL expert.  But recently, I needed a method to separate a name field into First, Middle, and Last Name.  Googling around, I found many methods, but none of worked for my specific format of First Middle Last (optional middle):

Roger J Carlson
Susan Carlson

So, after much fiddling, I came up with the following and I thought I'd share.

select
case len(rtrim(ltrim(v_name)))-len(replace(rtrim(ltrim(v_name)),' ',''))
     when 1 then parsename(replace(replace(rtrim(ltrim(v_name)), '.', ''), ' ', '.'), 1)
     when 2 then parsename(replace(replace(rtrim(ltrim(v_name)), '.', ''), ' ', '.'), 2)
     when 3 then parsename(replace(replace(rtrim(ltrim(v_name)), '.', ''), ' ', '.'), 3)
end as firstname,

case len(rtrim(ltrim(v_name)))-len(replace(rtrim(ltrim(v_name)),' ',''))
     when 2 then parsename(replace(replace(rtrim(ltrim(v_name)), '.', ''), ' ', '.'), 1)
     when 3 then parsename(replace(replace(rtrim(ltrim(v_name)), '.', ''), ' ', '.'), 2)
end as middlename,

case len(rtrim(ltrim(v_name)))-len(replace(rtrim(ltrim(v_name)),' ',''))
     when 1 then parsename(replace(replace(replace(rtrim(ltrim(v_name)), '.', ''), ' ', '.'), ',', ''), 2)
     when 2 then parsename(replace(replace(replace(rtrim(ltrim(v_name)), '.', ''), ' ', '.'), ',', ''), 3)
     when 3 then parsename(replace(replace(replace(rtrim(ltrim(v_name)), '.', ''), ' ', '.'), ',', ''), 4)
end as lastname

from mytable

I make no claim to have invented this method, just in adapting it to my specific format.