using regex in sqlite or mysql

Question

I'm using sqlite3 to try and find users who have an e-mail address that is either with Gmail, Yahoo or Hotmail. It needs to do this just on the basis of the first part of the domain, so I want any address that had the @yahoo to be accepted.

It appears from documentation that it is not possible to use a regular expression when querying an sqlite database. Is there any elegant way of doing something similar? It doesn't seem to be possible to use a "like/in" with multiple options (eg: LIKE (%@yahoo%, %@gmail%, %@hotmail%)?

Failing that, I may switch over to MySQL for a reg exp as I want to keep the solution simple and elegant and DB isn't a major factor. How would said regexp query be written in MySQL?

mck89 · Accepted Answer · 2011-04-11 07:39:24Z

2

You can't use multiple "LIKE" in that way but you can use:

(email LIKE "%@yahoo%" OR email LIKE "%@gmail%" OR ....)

answered Apr 11, 2011 at 7:39

mck89

19.3k17 gold badges93 silver badges111 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Michael Koper · Accepted Answer · 2011-04-11 07:54:35Z

0

you can use the way Nemoden is using or something like this (not tested)

WHERE email REGEXP '[\w\.]+@(yahoo|gmail|ymail|hotmail)\.(com|ru|co\.uk)'

This would be much faster because LIKE %string% OR ... is very slow and doesnt use any indexes(dont know if REGEXP uses indexes tho).

answered Apr 11, 2011 at 7:54

Michael Koper

9,8217 gold badges48 silver badges61 bronze badges

Comments

Nemoden · Accepted Answer · 2011-04-11 23:54:47Z

0

I think you might want something like this (RLIKE function):

WHERE email RLIKE '^[A-Z0-9._%+-]+@[A-Z0-9.-]+\.([A-Z]{2}|com|org|net|edu|gov|mil|biz|info|mobi|name|aero|asia|jobs|museum|travel)$'

If you use whatever_cs (case sensitive collation), use a-zA-Z instead of A-Z.

You can also get rid of ^ and $ in the regex. ^ means "starts with" and $ means "ends with"

UPDATE:

'.*@(gmail|hotmail|yahoo)\.[A-Z]{2,4}'

edited Apr 11, 2011 at 23:54

answered Apr 11, 2011 at 7:49

Nemoden

9,0966 gold badges45 silver badges66 bronze badges

4 Comments

Nemoden Over a year ago

Have no idea, why I can not comment Michaels post, so I commenting myy own: regular expressions do not utilize indexes! But still my approach is better then "LIKE ''... OR.... LIKE ... OR .... LIKE .... OR...." etc...

Darren Over a year ago

Thanks for this. How would this be edited to remove the requirement to look for a particular match of TLD at the end? IE: If I just wanted it to accept any domain.

Nemoden Over a year ago

There are plenitudes of regular expression on email addresses. Personally I like '^[A-Z0-9._%+-]+@[A-Z0-9.-]+\.(?:[A-Z]{2}|com|org|net|edu|gov|mil|biz|info|mobi|name|aero|asia|jobs|museum|travel)$' most. So, your RLIKE statement would look like I've edited above in my answer.

Darren Over a year ago

Thanks for your help here, but I'm actually not looking for it to do any checking except that after the @ there is a gmail, yahoo or hotmail. Can you tell me how this regexp/rlike would look for mysql?

Collectives™ on Stack Overflow

using regex in sqlite or mysql

3 Answers 3

Comments

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related