Login / Register  search  syndication  about

          Jason Buss' Blog

Jason Buss on SalesLogix development & customization, SQL, and more.

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!

  Attachment: ParseName.zip

What's This?
  
Bookmark and Share

About Jason Buss

   Jason is a senior application developer with Customer FX.



Related Content
   Adding a report link to the Common Tasks pane in Infor CRM (formerly Saleslogix)
When you are viewing an Entity Detail page, the Common Tasks pane on the right of the page provides a nu
Posted on Nov 12, 2014 by Jason Buss to Jason Buss' Blog
 
   Problem with the Clean Build Folders option in Application Architect
On occasion, it becomes necessary to clean out the build folders and deployed website prior to a build/de
Posted on Sep 16, 2014 by Jason Buss to Jason Buss' Blog
 
   Configuring and Packaging secured actions in Saleslogix 8.0
Using Secured Actions, you can restrict access to user interface elements in the Saleslogix web client.
Posted on Aug 12, 2014 by Jason Buss to Jason Buss' Blog
 
   Using an Array - SQL vs Crystal.
In data, an Array is used to store multiple values in a single variable.  I often use Arrays to qual
Posted on Jul 17, 2014 by George Jensen to George Jensen's Blog
 
   Debugging Custom Assemblies Using Visual Studio
Developing for Saleslogix web using external assemblies provides you with a lot of freedom when developin
Posted on Jun 10, 2014 by Jason Buss to Jason Buss' Blog
 
Comments

 

Ryan Farley said:

Great post Jason.

May 25, 2010 5:08 PM

Leave a Comment

(required)  
(optional)
(required)  
Add
All contents Copyright © 2014 Customer FX Corporation
Customer FX Corporation
2324 University Avenue West, Suite 115
Saint Paul, Minnesota 55114
Tel: 800.728.5783

  Follow @CustomerFX on twitter
Follow the best news, tips, and articles
  Subscribe to Customer FX on youtube
Watch SalesLogix tutorial videos from Customer FX
Login / Register