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