Creating valid email address from db

Need to split it apart, clean it and recombine

[JohnSka7]

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 [e-mail]

Pauly Shore IV needs to become [e-mail]

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!

[bmcgee]

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.

[docluv]

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