-4

Using SQL only, is there a way to query all values that are emails from a column of misc data? I tried like '%@%', but there could be texts like this: Fort Knox @ Room 123.

Edit: Regex is unavailable. I've also tried like '%@%.%' but did not cover cases with spaces.

drum
  • 5,416
  • 7
  • 57
  • 91
  • what flavor of sql? what else have you tried? – Daniel A. White Jul 29 '13 at 18:15
  • http://stackoverflow.com/questions/15523789/sql-email-verification-function-using-regex – Greg Jul 29 '13 at 18:15
  • http://stackoverflow.com/questions/801166/sql-script-to-find-invalid-email-addresses – PiLHA Jul 29 '13 at 18:15
  • 1
    sql database are for structured data. if you're storing "random" text in a field, then don't expect the database help you try to create order out of chaos. – Marc B Jul 29 '13 at 18:16
  • @MarcB I wish I was the one that designed the database...I'm just a minion for an organization with existing systems. – drum Jul 29 '13 at 18:24

4 Answers4

1

Here is SQLFiddle for you. 

 SELECT *
  FROM TABLENAME
  WHERE email LIKE '%@%.%'
    AND email NOT LIKE '% %';
Naveen Kumar Alone
  • 7,536
  • 5
  • 36
  • 57
0

If you don't mind using CLR functions, I'd suggest writing a .NET method which uses a proper regular expression (you can find various ones online) to validate the email address, then calling that function as part of your query.

select * from whatever
where dbo.IsThisAnEmailAddress(myColumn) = 1
Joe Enos
  • 39,478
  • 11
  • 80
  • 136
0

I would use regular expressions in your query to get at the emails. There are tons of links on this site to valid email regexes.

Brian Maupin
  • 745
  • 4
  • 17
0

I agree with others, RegEx is better, however, if not available, try the following

WHERE fieldName LIKE '%@%' 
      AND fieldName LIKE '%.%' 
      AND charindex(' ',fieldName)=0

It's not great, and slow, but should get you pretty close. I.E Contains both an @ and an . and no spaces...

SQLFiddle: http://www.sqlfiddle.com/#!3/5f6d8/1

Sparky
  • 14,967
  • 2
  • 31
  • 45