CODEWITHSUNDEEP
mysqlsqlsqlite
Darren
Darren

Reputation: 3059

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?

Upvotes: 0

Views: 2251

Answers (3)

Nemoden
Nemoden

Reputation: 9056

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

Upvotes: 0

Michael Koper
Michael Koper

Reputation: 9781

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

Upvotes: 0

mck89
mck89

Reputation: 19231

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

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

Upvotes: 2

Related Questions