asked on

Splitting contact names into component Title/Forename/Surname

Hi, just being lazy here!

Has anyone done a SQL stored procedure that will take a person's name and split it into its component parts?

E.G.

Mr Frederick A Bloggs

Would split this into:

Title: Mr
Forename: Frederick
Initials: F A
Surname: Bloggs

Ideally be intelligent enough to also spot that Mr Bloggs should only populate the Title and Surname.

I have done something similar that uses the charindex and patindex functions, I just didn't want to have to reinvent the wheel if someone else has already done it!

Thanks.

Einstine98

we have but can't post it... IP rights of the company... but I can help you out in doing developing something if you need help.

pauldonson

ASKER

OK, thanks Einstine98

This is my start (to establish the title)

select top 100 contact_ID, contact_name into #Contacts

from
TBL_Contacts

declare      @Contact_name as varchar(75),
      @Contact_title as varchar(10),
      @Contact_forename as varchar(50),
      @Contact_initials as varchar(10),
      @Contact_surname as varchar(50),
      @Contact_ID as int,
      @tmpTitle as varchar(10),
      @tmpCharIndex as int,
      @tmpContact_name as varchar(75)

while (select count(*) from #Contacts) > 0
begin

Select top 1 @Contact_ID = Contact_ID, @tmpContact_name = Contact_name from #Contacts

set @Contact_name = @tmpContact_name

--Check if Mr/Mrs/Miss/Miss/Dr is present
Set @tmpCharIndex = charindex(' ', @tmpContact_name)
If @tmpCharIndex > 0
      begin
      Set @TmpTitle = substring(@tmpContact_name, 1, @tmpCharIndex)
      If       @TmpTitle = 'Mr' or
            @TmpTitle = 'Mrs' or
            @TmpTitle = 'Miss' or
            @TmpTitle = 'Ms' or
            @TmpTitle = 'Dr'
            Begin
            Set @Contact_title = @TmpTitle
            Set @tmpContact_name = substring(@tmpContact_name, @TmpCharIndex + 1, Len(@tmpContact_name)-@TmpCharIndex)
            end
      end

Print convert(varchar(10),@Contact_ID) + ',' + isnull(@Contact_name,'') + ',' + isnull(@Contact_title,'')
Delete from #Contacts where contact_id = @Contact_ID

end

drop table #Contacts

Would you say I am in the right direction? It does seem very long winded! I am just testing at the moment so the results are just being printed in SQL query analyser.

ASKER CERTIFIED SOLUTION

Einstine98

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

pauldonson

ASKER

Yep, 2000 I'm afraid. I think you are right - User-Defined Functions are the way to go!

Thanks for the tip. I'll keep you posted,.

pauldonson

ASKER

Sorted it. Your tip about creating a sql function got me going. I have created different functions for each of the components.

Thanks for your help.