SQLTeam.com | Weblogs | Forums

Eliminating periods to find email pattern in SQL


#1

Hi everyone,

How would I search for an email pattern when the emails looks like this?

In this example I would like to search and find every email that is like marc123@mail

Example:

m.a.r.c1.23@mail.com
ma.r.c.123@mail.com
m.ar.c12.3@mail.com
mar.c1.23@mail.com

Thank you.


#2

DECLARE @mail table ( email varchar(255) ); INSERT @mail VALUES ( 'm.a.r.c1.23@mail.com' ) , ( 'ma.r.c.123@mail.com' ) , ( 'm.ar.c12.3@mail.com' ) , ( 'mar.c1.23@mail.com' ); SELECT email FROM @mail WHERE Replace(email, '.', '') = 'marc123@mailcom';

This really is what you asked for.

DECLARE @mail table ( email varchar(255) );
INSERT  @mail
VALUES
        ( 'm.a.r.c1.23@mail.com' )
      , ( 'ma.r.c.123@mail.com' )
      , ( 'm.ar.c12.3@mail.com' )
      , ( 'mar.c1.23@mail.com' );
SELECT
    email
FROM
    @mail
WHERE
    Replace(email, '.', '') LIKE 'marc123@mail%';

#3

Thank you so much Jotorre.