0

I have a table that has a column named 'bio', which is a string of max length 255. Inside this bio I want to search if there's an email contained in it. I want to count how many entries in the table that has an email in the bio column. Is such thing possible to be done via SQL?

To add more clarification, bio is a free form text and I am looking to match the email pattern, and not a specific email.

adit
  • 32,574
  • 72
  • 229
  • 373
  • Are you looking for a specific email, or anything that matches the email pattern? – Barmar Sep 15 '13 at 03:40
  • Your question is too broad - you should show us a few rows as an example. If it either contains an email or NULL you can simply use the condition `AND bio not null` unless you're looking for a specific email address, then I would use `LIKE`... – Nir Alfasi Sep 15 '13 at 03:41
  • @alfasin From the name, I guess `bio` is free form text that the user posts as their biography (like SO profiles), and he wants to know how many users have put an email address somewhere in their bio. – Barmar Sep 15 '13 at 03:42
  • @Barmar anything that matches an email pattern – adit Sep 15 '13 at 03:42
  • @Barmar that makes sense but it's a guess while it should be clearly specified in the question if it's like that indeed. – Nir Alfasi Sep 15 '13 at 03:43
  • @Barmar you got that exactly right – adit Sep 15 '13 at 03:43
  • 1
    @adit sounds like you can use regex to match an email pattern (you'll find plenty of complex regex that does that work if you'll Google it). – Nir Alfasi Sep 15 '13 at 03:44
  • For OP about regular expressions in MySQL: http://dev.mysql.com/doc/refman/5.7/en/regexp.html – PM 77-1 Sep 15 '13 at 03:50

1 Answers1

2

You can use a query like this:

select count(*)
from mytable
where bio REGEXP '<emailregexp>'

Replace <emailregexp> with the regular expression you're using to match email patterns. There are dozens of answers for that on SO, e.g.

Using a regular expression to validate an email address

However, most of these questions are about validating an email, which assumes that the entire input field is supposed to be a single email. Make sure you remove the ^ and $ anchors for the ends of the input so that yo look for an email anywhere in the field.

Community
  • 1
  • 1
Barmar
  • 741,623
  • 53
  • 500
  • 612