using regex in sqlite or mysql

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?

---

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

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

---

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

---

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