SQLTeam.com | Weblogs | Forums

Extract customers first and last name from a string


how can I extract customers first and last name from the following

Mr. Aul Cin Aadullah
Ms Stacy K. Burke, CAP
Mr. Step Don Coates, CAE, CDA, CEE

to read
Aul Aadullah
Stacy Burke
Step Coates

basically removing the Mr, Ms and suffix (CAE, CDA , CEE)

substring would not work because each length is different


it depends. The list of possible prefixes is large and sometimes multipart (The Right Honorable Sir William Wallace). Surnames can be multipart (e.g. Ralph Vaughn Williams). The list of suffixes is also large and can be multiplart (Fred Flinstone III,Bsc, MCSA, MCSE-DP)

Solving the problem generally is difficult though it has been solved before (e.g. Parser)

So it really depends on how thorough you want to be


It would depend on how clean the data is and how you want the end result.
A few questions:
1 - Once extracted, Do you need to be able to split the first name from the surname or would it be sufficient to have it as one field eg FullName?
2 - Is the data from one source or even if from multiple sources, have the titles been selected from predefined lists or have they been manually typed?
3 - Is there always a comma at the end of the surname? If no, is there a comma when there is trailing data?

Depending on the answers to the above, it could be a relatively simple job but on the other hand could become a very difficult job. The cleaner the data the easier the job :smile:


I found avway around it by using the replace function thanks