0

I'm trying to write a mysql query to detect three or more periods in a email string. Example

gr.em.test.t@domain.com

I'm thinking I could use some sort of mysql like query, but I'm not sure how to best write the regex. Any thoughts?

SELECT MyColumn
From MyTable
WHERE MyColumn like
Code Junkie
  • 7,602
  • 26
  • 79
  • 141
  • 1
    @Barmar This isn't a duplicate of the question you linked. The question you linked is asking how to count how many occurrences of a given string are in a column. The OP for this question is asking how to return rows where a certain column has X number occurrences of a string. While similar, it's not a duplicate as the question you linked doesn't actually answer the OP's question. – michael Jan 28 '16 at 03:14
  • @michael I agree, this isn't a duplicate. – Code Junkie Jan 28 '16 at 03:14
  • OK, although if you can count them, you can use `WHERE >= 3`. – Barmar Jan 28 '16 at 03:17
  • True. I definitely agree with you that question could be helpful to the OP. I just felt it didn't fully answer their question. – michael Jan 28 '16 at 03:18

1 Answers1

1

No need for any type of regular expression, simply use the LIKE clause like you have, so you're on the right track. The following query should give you what you want:

SELECT MyColumn FROM MyTable WHERE MyColumn LIKE "%.%.%.%";

michael
  • 748
  • 4
  • 10
  • how can I make it >= 3 though? I'm having an issue with scammers cluttering my database with these bogus emails, so I'd like to clean them up. – Code Junkie Jan 28 '16 at 03:12
  • @CodeJunkie The query I provided will handle `>= 3`. The `%` act as a wildcard, which will match periods as well. There has to be at least 3 periods for it to match, but it also matches rows with more than 3 periods. – michael Jan 28 '16 at 03:15
  • Okay great, I was thinking it would match only 3 for some reason. Thanks. – Code Junkie Jan 28 '16 at 03:16