Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Help with Derived Column To Split-Up An Individual's Name into Separate Parts


Help with Derived Column To Split-Up An Individual's Name into Separate Parts

Author
Message
brad.mccollum
brad.mccollum
Valued Member
Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)

Group: General Forum Members
Points: 73 Visits: 329
I'm working on an SSIS package for a client. They receive files exported from their clients and they don't put any restrictions on how the data is provided to them via the Excel files they send, so 99.9% of the files arrive with a "Full Name" field instead of individual "First Name, "Middle Initial/Name", "Last Name", and "Surname" columns.

The names are generally in this pattern, which has a few possible variances as you can see as far as how the names are present in the column:

Adams, Jr., Dwight
([Last Name], [Surname], [First Name])

Allen, Frederick
([Last Name], [First Name])

Bishop, Joseph A.
([Last Name], [First Name] [Middle Initial])

Butler, III, Lamarrick J.
([Last Name], [Surname], [First Name] [Middle Initial])

Nicholson, Tina Michelle
([Last Name], [First Name] [Middle Name])

Cagle, Sr., Kimzey Alan
([Last Name], [Surname], [First Name] [Middle Name])

I'm trying to determine exactly how to handle these varying scenarios as far as how the names will be present in the single column as to appropriate & accurately split them into their various parts.

I wasn't sure if I should go with parsing formulas for each component of the full name directly within the derived column task, or if I should write a function that accepts the full name as an incoming parameter and then the parts of the name are parsed that way and somehow returned to the derived column task. I'm trying to tackle this the best way possible and I'm seeking suggestions.

I've seen a few code samples out there for parsing through a full name into the various individual parts of the name, but I haven't run across any specific examples yet that deal with full names structured exactly the way that I've shown above in the examples that they can be structured in the incoming files.

Thanks in advance.
Michael Valentine Jones
Michael Valentine Jones
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3254 Visits: 11771
As you probably know already, this is a very complex problem, especially when you are dealing with multipart names, non-western names, etc.

You may want to invest in commercial software that is designed to handle this problem:
http://www.softwarecompany.com/netgender-api.html
Phil Parkin
Phil Parkin
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8310 Visits: 19454
The complexity of the problem means that a derived column solution is unlikely to be practical.

A Script Component would allow you to write code with as much complexity as you need, to handle the various combinations you have described, as long as you can come up with a set of logic rules to apply in order to perform the parsing.


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search