How can I use a select query in SQL to find a list of email addresses that only contain 6 random numbers?

Question

I’m currently looking for a way to use a SQL query in order to find a list of all email addresses in our DB has only 6 random numbers and then ‘@gmail.com’.

Example:

email

----------

123456@gmail.com
324522@gmail.com

Here is what I tried:

select email 
from customers
where email Not like '%^[0-9]%'

When I run this, all emails appear even the ones without any numbers in them.

select email,
SPLIT_PART(email, '@',1) as username, 
SPLIT_PART(email, '@',2) as domain,
(case when username not like '%^[0-9]%' then 'Incorrect' else 'Correct' End) as format
from customers
where domain = 'gmail.com'
and format = 'Correct'

I tried this as well, for all emails even if they had numbers in them they appeared as Incorrect.

It seems like the numbers in the columns are not being recognized and I'm not sure how to fix that. The column format is Varchar

`SELECT email FROM customers WHERE email LIKE '[0-9]{6}%'`? :) — Mehmed, Jun 26 '19 at 05:25
when I run that I get 'No Rows Returned' :( I know these emails exist. It seems like it's not recognizing there are numbers. — JG733, Jun 26 '19 at 05:28
The `like` operator is not the same as regex matching in any DBMS I've ever used. It sounds like you want regex matching, not just a simple `like`. No clue what Mode Analytics is, but just search for how to do regex matching in it. — Josh Eller, Jun 26 '19 at 14:29

score 2 · Answer 1 · answered Jun 26 '19 at 17:36

2

I spoke with Mode Analytics and it turns out my DB is Redshift this is how I was able to get this work:

select email
from customers
where email similar to '[0-9]{6}@gmail.com'

Thanks everyone for your help!

answered Jun 26 '19 at 17:36

JG733

31
4

How can I use a select query in SQL to find a list of email addresses that only contain 6 random numbers?

1 Answers1