Welcome to WebmasterWorld Guest from 18.205.176.85

Forum Moderators: ocean10000

Message Too Old, No Replies

Creating valid email address from db

Need to split it apart, clean it and recombine

     
6:33 pm on Aug 20, 2008 (gmt 0)

New User

10+ Year Member

joined:Apr 25, 2008
posts: 13
votes: 0


Hello everyone,

I'm working on a small piece of VB.net that is interacting with my MySQL database. I'm using SSIS to pull data from an MSSQL database, clean it up and then, using a script component, put it into the MySQL database.

I've gotten it all working except for the email address. If an address is not in the original (MSSQL) database, I want the script component to combine the first name and last name columns, add our web address, and then use that.

My script so far looks like this:


If (Row.STAFOrganizationEmailAddress = Nothing Or Row.STAFOrganizationEmailAddress_IsNull Or Row.STAFOrganizationEmailAddress = String.Empty) Then strEmail = Row.STAFFirstName + "." + Row.STAFLastName + "@mysite.com" Else strEmail = Row.STAFOrganizationEmailAddress

The problem is that I need to do an additional cleaning on the address. It seems that our payroll people (where the data is coming from) include a suffix in the last name (ie Jr., Sr., Esq, III, IV, etc). I need to be able to break apart the last name field based on spaces, see if each part meets up with one of our known suffixes and if so, drop it, then recombine the good parts without spaces to form the lastname for inclusion in the email address.

As an example:

Derrick Mc Dreamy needs to become Derrick.McDreamy@mysite.com

Pauly Shore IV needs to become Pauly.Shore@mysite.com

I've been thrown to the wolves with this one: our vb guy is too busy to help and I'm a PHP programmer, so I'm doing my best but I'm really stuck here. Any help/direction would be really appreciated.

Thanks!

2:31 am on Aug 23, 2008 (gmt 0)

Full Member

10+ Year Member

joined:May 14, 2001
posts:262
votes: 0


Use Split() with " " as the second parameter and then parse the array and check each item. Concatenate the pieces back together as you see fit.
2:59 pm on Oct 16, 2008 (gmt 0)

New User from US 

10+ Year Member

joined:May 31, 2004
posts:31
votes: 3


For something like that I like to use Regular Expressions. Start with www.regexlib.com.