Query to return only rows that have valid emails and output only the email

user_7828450 0
Viewed 238

I have a old table that has applicant info including the person who interviewed them Name|Phone|Email|Addr|Interv|IntervEmail

unfortunately the interviewer emails are a mess. Some blank some have phone numbers some have spaces or other special characters like this.is.a.mess@anydamnplace.com / 999-222-5555 home or home 999-222-5555, this.is.a.mess@anydamnplace.com

What I would like is be able to get all rows with interviewer emails from the table but only the email and not the other junk.

any help appreciated

example From this

user   | home_phone | applicant_email           | addr      | intrvwr_nm | intrvwr_email
-------+------------+---------------------------+-----------+------------+---------------------------------------------------------------------
Jane   | 1234567890 | someemail@anydamplace.com | 123 here  | Jeff       | jeff@surething.net / 321589364
Larry  | 1234567890 | someemail@anydamplace.com | 123 there | Eddie      | gjfgn@anotherplace.net / 2321.654.9872
harry  | 1234567890 | someemail@anydamplace.com | 456 here  | Murray     | Admissions Field Representative/murray@anotherplace.com/212-222-1111
Carrie | 1234567890 | someemail@anydamplace.com | 456 there | Del        | dely@school.edu
Joe    | 1234567890 | someemail@anydamplace.com | 123 here  | James      | 1234567890/sometext, this.is.a.mess@anydamnplace.com

To this

user   | home_phone | applicant_email           | addr      | intrvwr_nm | intrvwr_email
-------+------------+---------------------------+-----------+------------+---------------------------------------------------------------------
Jane   | 1234567890 | someemail@anydamplace.com | 123 here  | Jeff       | jeff@surething.net
Larry  | 1234567890 | someemail@anydamplace.com | 123 there | Eddie      | gjfgn@anotherplace.net
harry  | 1234567890 | someemail@anydamplace.com | 456 here  | Murray     | murray@anotherplace.com
Carrie | 1234567890 | someemail@anydamplace.com | 456 there | Del        | dely@school.edu
Joe    | 1234567890 | someemail@anydamplace.com | 123 here  | James      | this.is.a.mess@anydamnplace.com
tsql sql-server sql regex
1 Answers

Try running this query. It would catch most of the valid email address:

SELECT a.email 
FROM
(
SELECT value as email  
FROM applicant_Table  
    CROSS APPLY STRING_SPLIT(intrvwr_email, ' ') 
) as a
where a.email like '%_@__%.__%'

Please take into account that:

  1. This only works for SQL Server 2016 or above.
  2. The delimiter is a space.

You can add more columns to the query (like the row ID) so you get to know in which row is stored every email address you're getting.

user_128089050
Related