SQL Function for parsing name values

 In a recent project, I had to create a SQL Server Integration Services package to basically copy contact information (which was being stored in the Account.Account field) from the Account table into the Contact table.  Since the Contact name was being stored in it’s entirety within the account field, I needed a way to parse the Contact information to populate the individual contact name fields in the contact table.

I created a scalar function called ParseName to accomplish this: (I’ve attached a zip containing the .sql file as well)

 CREATE function [dbo].[ParseName](@NameString varchar(100), @NameFormat varchar(20))
returns varchar(100) as
begin

--ParseName parses a Name String into its component parts
--and returns it as requested.
--This function assumes the data source maintains a standard format of
--'Prefix Firstname Middlename Lastname Suffix'
--
--@NameString is the raw value to be parsed.
--@NameFormat is a string that defines the output format. Each letter in the string
--represents a component of the name in the order that it is to be returned.

-- P = Full prefix
-- p = Abbreviated prefix
-- F = First name
-- f = First initial
-- M = Middle name
-- m = Middle initial
-- L = Last name
-- l = Last initial
-- S = Full suffix
-- s = Abbreviated suffix
-- . = Period
-- , = Comma
-- [ ] = Space

--Example1: select dbo.Parsename('Doctor Bobo Lee Brown Esquire', 'L, p. F m. s.')
--Result: 'Brown, Dr. Bobo L. Esq.'

--Example2: (Full contact name stored in SLX Account table)
-- select
-- dbo.Parsename(ACCOUNT, 'F') as FIRSTNAME
-- , dbo.Parsename(ACCOUNT, 'L') as LASTNAME
-- from ACCOUNT
--
--Returns: FIRSTNAME, LASTNAME
-- --------------------
-- 'Bobo', 'Brown'

Declare @Prefix varchar(20)
Declare @FirstName varchar(20)
Declare @MiddleName varchar(30)
Declare @LastName varchar(30)
Declare @Suffix varchar(20)
Declare @TempString varchar(100)

--String Prep - We want to remove any periods, double spaces and commas
set @NameString = rtrim(ltrim(replace(@NameString, '.', '. ')))
while charindex(' ', @NameString) > 0 set @NameString = replace(@NameString, ' ', ' ')
while charindex('.', @NameString) > 0 set @NameString = replace(@NameString, '.', '')
while charindex(',', @NameString) > 0 set @NameString = replace(@NameString, ',', '')

--Get Prefix and strip it out of the string... Add additional prefixes as needed
set @TempString = rtrim(left(@NameString, charindex(' ', @NameString + ' ')))
if @TempString in ('MR', 'MRS', 'MS', 'DR', 'Doctor') set @Prefix = @TempString
if len(@Prefix) > 0 set @NameString = ltrim(right(@NameString, len(@NameString) - len(@TempString)))

--Get Suffix and strip it out of the string... Add additional suffixes as needed
set @TempString = ltrim(right(@NameString, charindex(' ', Reverse(@NameString) + ' ')))
if @TempString in ('Jr', 'Sr', 'II', 'III', 'Esq', 'Junior', 'Senior') set @Suffix = @TempString
if len(@Suffix) > 0 set @NameString = rtrim(left(@NameString, len(@NameString) - len(@TempString)))

--Strip Lastname out of the string
set @LastName = ltrim(right(@NameString, charindex(' ', Reverse(@NameString) + ' ')))
set @NameString = rtrim(left(@NameString, len(@NameString) - len(@LastName)))

--Check Last name to see if it has two parts... Add additional values as needed
set @TempString = ltrim(right(@NameString, charindex(' ', Reverse(@NameString) + ' ')))
if @TempString in ('VAN', 'VON', 'MC', 'Mac', 'DE')
begin
set @LastName = @TempString + ' ' + @LastName
set @NameString = rtrim(left(@NameString, len(@NameString) - len(@TempString)))
end

--Get FirstName and strip it out of the string
set @FirstName = rtrim(left(@NameString, charindex(' ', @NameString + ' ')))
set @NameString = ltrim(right(@NameString, len(@NameString) - len(@FirstName)))

--Anything left is MiddleName
set @MiddleName = @NameString

--Build output string
--You will need to add Long/Short versions of Prefixes and Suffixes to the appropreate section
set @TempString = ''
while len(@NameFormat) > 0
begin
set @TempString = @TempString +
case ascii(left(@NameFormat, 1))
when '80' then case @Prefix
when 'Dr' then 'Doctor'
when 'Rev' then 'Reverend'
else isnull(@Prefix, '')
end
when '70' then isnull(@FirstName, '')
when '77' then isnull(@MiddleName, '')
when '76' then isnull(@LastName, '')
when '83' then case @Suffix
when 'Jr' then 'Junior'
when 'Sr' then 'Senior'
when 'Esq' then 'Esquire'
else isnull(@Suffix, '')
end
when '112' then case @Prefix
when 'Doctor' then 'Dr'
when 'Reverend' then 'Rev'
else isnull(@Prefix, '')
end
when '102' then isnull(left(@FirstName, 1), '')
when '109' then isnull(left(@MiddleName, 1), '')
when '108' then isnull(left(@LastName, 1), '')
when '115' then case @Suffix
when 'Junior' then 'Jr'
when 'Senior' then 'Sr'
when 'Esquire' then 'Esq'
else isnull(@Suffix, '')
end
when '46' then case right(@TempString, 1)
when ' ' then ''
else '.'
end
when '44' then case right(@TempString, 1)
when ' ' then ''
else ','
end
when '32' then case right(@TempString, 1)
when ' ' then ''
else ' '
end
else ''
end
set @NameFormat = right(@NameFormat, len(@NameFormat) - 1)
end

Return @TempString
end

GO

 

This function will allow you to parse contact values and return them in any way you would like.  It’s not all inclusive in terms of available prefixes/suffixes, etc.  so, please feel free to use/modify this function as needed for your situation.

Thanks for reading!

ABOUT THE AUTHOR

Jason Buss

Jason is a senior application developer with Customer FX.

1 Comment

Submit a Comment

Your email address will not be published. Required fields are marked *

Subscribe To Our Newsletter

Join our mailing list to receive the latest Infor CRM (Saleslogix) news and product updates!

You have Successfully Subscribed!