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.