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
   Setting the Defaults on the Insert Account/Contact Duplicate Record View
When creating a new Account/Contact record in Saleslogix web, you have the ability to check for duplicat
Posted on Mar 10, 2014 by Jason Buss to Jason Buss' Blog
 
   Adding string formatting to values using WebEntityBinding
In a recent project, I needed to round numeric values displayed in the Opportunity Snapshot.  This
Posted on Feb 05, 2014 by Jason Buss to Jason Buss' Blog
 
   SQL Stored Procedure to Create SalesLogix Table ID Values
Most of the SalesLogix systems I see now days no longer use remote users. Instead, all users VPN into a c
Posted on Dec 23, 2013 by Ryan Farley to Ryan Farley's Blog
 
   Updating text within Textblob fields in SalesLogix
I had a request to post on how you could replace characters within a blob field in the SalesLogix databas
Posted on Apr 19, 2012 by Jason Buss to Jason Buss' Blog
 
   Summary and GroupBy of data using IRepository and projections
In the SalesLogix web client, you don't really have the same old options regarding Queries and Datase
Posted on Apr 05, 2012 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