Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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 <>

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

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.

share|improve this question
I've changed the tag of this question, it's not related to normalization, but to data parsing. – Marian Feb 8 '11 at 10:06
~ Are you looking to do the following? note: pseudocode, not real code!! if contains('<') and contains('>') then return field.substring(indexofLast('<'),indexofLast('>') ) else return field? – jcolebrand Feb 8 '11 at 14:47

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.

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)

share|improve this answer

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 <>'

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.

share|improve this answer

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 <>' Email FROM dual
   SELECT 'Jane Doe <>' Email FROM dual
   SELECT 'Bob Zeek <>' Email FROM dual
   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.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.