How do I normalize and transform field text algorithmically

Question

I've got names and emails exported from one sales system that I am trying to move into my email service provider. I have to normalize the data.

The email field has data like this:

John Smith <jsmith@gmail.com>

I want to algorithmically turn all email field entries into this:

jsmith@gmail.com

Do you know the best tool, script, process or excel function to do this? It's racking my brain, because I don't want to do it manually.

Answer 1

Example SQL Script
For the specific example of parsing a name into it's separate elements see (SO question): How can I parse the first, middle and last name from a full name field in SQL?

The accepted answer is a fantastic example of how to roll your own algorithm to separate out your names from emails.

Tools
There are some out there, I'll update this answer later when I can have some time to find (or please edit this answer if you have found some)

Note: No matter what method you use, do a sanity check validation of your results, most good methods will be able to accurately parse 90+% of your data, the real trick is how to identify the <10% that have not been correctly parsed (that can be as simple as doing a sanity check and scanning the results)

Answer 2

If you have an instance of SQL Server close to you, I'd suggest you to import the excel file in a table and then parse the data after. The import is done through a pretty straightforward wizard - right click a database - tasks - import data - chose your source provider (excel) and then your file. After that you should be able to run a query to do it. I've built a simple one for this particular example:

DECLARE @x VARCHAR(100) = 'John Smith <jsmith@gmail.com>'

SELECT CHARINDEX('<', @x) AS Start_, CHARINDEX('>', @x) AS End_,

SUBSTRING(@x, CHARINDEX('<', @x)+1, CHARINDEX('>', @x)-CHARINDEX('<', @x)-1) as Mail

But Andrew is right with the sanity check. You shouldn't trust only your script. Some data can have some another format.

Answer 3

You didn't say what database you need to do this in. If it is Oracle you could use one of the following methods:

WITH data1 AS (
   SELECT 'John Smith <jsmith@gmail.com>' Email FROM dual
   UNION ALL
   SELECT 'Jane Doe <jdoe@hotmail.com>' Email FROM dual
   UNION ALL
   SELECT 'Bob Zeek <bobzeek123423@yahoo.com>' Email FROM dual
)
SELECT 
   substr(Email,instr(Email,'<')+1,length(Email) - instr(Email,'<') - 1) Conversion1,
   translate(regexp_substr(Email,'<.*>',1,1),'*<>','*') Conversion2,
   translate(substr(email,instr(email,'<')+1),'*>','*') Conversion3
FROM data1;

For data outside the database you can use an External Table for a plain text file or a database link for excel data.