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?


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!

Who is Participating?
Einstine98Connect With a Mentor Commented:
before we go deep into this are using SQL 2000 or SQL 2005? if 2005... just do it using:

1. SSIS : if you can
2. CLR : Develop a function using VB or C# they are FAR better in performance and eas of use...

3. If you are stuck with 2k :-) the way I would approach is :

Create 3 user defined functions


You Do something like this in the query :

SELECT fnMr (Contact_Name), fnFirstName (Contact_Name), fnLastName (ContactName)
FROM MyTable

fnMR : Code that would check for ... you guessed it....etc

You can also create one function (for code managability, passing a parameter that identifies what you're looking for

fnSplitname (Contact_Name, 'Mr')

Hope this helps... if you need more help on each function let me know
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.
pauldonsonAuthor Commented:
OK, thanks Einstine98

This is my start (to establish the title)

select top 100 contact_ID, contact_name into #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

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
      Set @TmpTitle = substring(@tmpContact_name, 1, @tmpCharIndex)
      If       @TmpTitle = 'Mr' or
            @TmpTitle = 'Mrs' or
            @TmpTitle = 'Miss' or
            @TmpTitle = 'Ms' or
            @TmpTitle = 'Dr'
            Set @Contact_title = @TmpTitle
            Set @tmpContact_name = substring(@tmpContact_name, @TmpCharIndex + 1, Len(@tmpContact_name)-@TmpCharIndex)
Print convert(varchar(10),@Contact_ID) + ',' + isnull(@Contact_name,'') + ',' + isnull(@Contact_title,'')
Delete from #Contacts where contact_id = @Contact_ID


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.
pauldonsonAuthor Commented:
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,.
pauldonsonAuthor Commented:
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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.