Possible with MYSQL replace()?

Question

Is the following possible with just a sql query or do I need to create a php routine?

I have a database of members with gmail addresses, most of which contain 1 or more '.' (dots) in the address before the '@'

For the purposes of thwarting duplicate/multiple signups I am creating a new column that will contain the 'clean' gmail address so I can make comparisons going forward on new registrations.

Example... I have rows which the following gmail addresses are actually all the same in the eyes of gmail.

[email protected]
[email protected]
[email protected]
[email protected]

I know replace() can simply replace something with something, but I want to replace all dots before the @

Is there a sql function/statement I can use to clean up all the dots before the '@' or do I have to create a php routine to achieve this?

Just out of curiosity.. but [email protected] is definatly another email address then [email protected]. Wouldn't "fixing" these email adresses actually break the data in your database? — Damien Overeem
– Damien Overeem, Commented Nov 20, 2013 at 10:39
@DamienOvereem. For gmail, those are the same. that's what he is trying to say. sending one email to those 4 mails will always send it to the same unique email address of [email protected] — Filipe Silva
– Filipe Silva, Commented Nov 20, 2013 at 10:41
@DamienOvereem No they are the same. Google created this for filtering options. Dots before the @ are irrelevant. All the emails I show above will all go to [email protected]. Very similar a gmail usr can also add keywords with a '+'. Example, [email protected] will go to [email protected]. — user1687533
– user1687533, Commented Nov 20, 2013 at 10:47

vkamayiannis · Accepted Answer · 2013-11-20 10:48:18Z

1

Try this:

select concat(replace(substring(email, 1, locate('@', email)-1), '.', '') , substring(email, locate('@', email), length(email)))

answered Nov 20, 2013 at 10:48

vkamayiannis

7353 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user1687533 Over a year ago

This was the first one I tried and it did indeed give me the results I wanted. Thank you!

clops · Accepted Answer · 2013-11-20 10:44:29Z

1

Try this

SELECT CONCAT(REPLACE(LEFT(email,LOCATE('@',email)-1), '.', ''),RIGHT(email, LOCATE('@',email))) FROM your_table.

answered Nov 20, 2013 at 10:44

clops

5,2656 gold badges44 silver badges54 bronze badges

1 Comment

clops Over a year ago

The basic algorithm here is as follows: split the address on the '@' sign, replace all dots in the left part, and concat the parts back together.

Steph Locke · Accepted Answer · 2013-11-20 10:44:33Z

0

You can identify the section before the @, replace the dots, and then combine it with everything after the @:

SET @EMAIL='[email protected]';
SELECT CONCAT(REPLACE(LEFT(@EMAIL,INSTR(@EMAIL,'@')),'.',''),RIGHT(@EMAIL,CHAR_LENGTH(@EMAIL)-INSTR(@EMAIL,'@')))

The LEFT and RIGHT are used to split the string, the INSTR used to determine where to split the string, and the CONCAT chucks it all back together again. These sorts of things can be built up a step at a time - I started with finding my @, then getting the stuff to the LHS and so on...

answered Nov 20, 2013 at 10:44

Steph Locke

6,1964 gold badges44 silver badges79 bronze badges

Comments

h2ooooooo · Accepted Answer · 2013-11-20 10:49:55Z

0

You can use a mix of CONCAT, REPLACE, SUBSTRING and LOCATE:

SELECT
    `email`,
    CONCAT(
        REPLACE(
            SUBSTRING(
                `email`,
                1, 
                LOCATE(
                    '@', 
                    `email`
                ) - 1
            ),
            '.', 
            ''
        ),
        '@',
        SUBSTRING(
            `email`,
            LOCATE(
                '@', 
                `email`
            ) + 1
        )
    ) as `email_clean`
FROM
    `emails`

SQLFiddle

┌──────────────────────┬─────────────────────┐
│        EMAIL         │     EMAIL_CLEAN     │
├──────────────────────┼─────────────────────┤
│ [email protected]      │ [email protected]     │
│ [email protected]  │ [email protected] │
│ [email protected] │ [email protected]  │
│ [email protected]  │ [email protected]  │
│ [email protected]          │ [email protected]         │
└──────────────────────┴─────────────────────┘

edited Nov 20, 2013 at 10:49

answered Nov 20, 2013 at 10:44

h2ooooooo

39.6k8 gold badges70 silver badges108 bronze badges

2 Comments

DevelopmentIsMyPassion Over a year ago

It gives wrong output. I inserted record [email protected] and it returns [email protected]. Check this fiddle sqlfiddle.com/#!2/e92a2/1

h2ooooooo Over a year ago

@AshReva Cannot confirm? Did you check your link?

Community · Accepted Answer · 2017-05-23 12:12:41Z

UPDATE some_table SET some_field = REPLACE(some_field, '&lt;', '<')

Please see the Ref URL : How can I use mySQL replace() to replace strings in multiple records?

hope this is useful for all of us.

At a very generic level

UPDATE MyTable

SET StringColumn = REPLACE (StringColumn, 'SearchForThis', 'ReplaceWithThis')

WHERE SomeOtherColumn LIKE '%PATTERN%'

In your case you say these were escaped but since you don't specify how they were escaped, let's say they were escaped to GREATERTHAN

UPDATE MyTable

SET StringColumn = REPLACE (StringColumn, 'GREATERTHAN', '<')

WHERE articleItemLIKE '%GREATERTHAN%'

Since your query is actually going to be working inside the string, your WHERE clause doing its pattern matching is unlikely to improve any performance - it is actually going to generate more work for the server. Unless you have another WHERE clause member that is going to make this query perform better, you can simply do an update like this:

UPDATE MyTable

SET StringColumn = REPLACE (StringColumn, 'GREATERTHAN', '<')

Coming in from a completely different angle:

You can do this when you select the data (not when you do save it)

So instead of :

SELECT MyURLString From MyTable

You can do

SELECT REPLACE (MyURLString, 'GREATERTHAN', '<') as MyURLString From MyTable

NoLifeKing · Accepted Answer · 2013-11-20 10:52:05Z

0

Here's a query which strips the first dots from the "name"-part in the mail adress

SET @mailAddress := '[email protected]';
SELECT @mailAddress, 
    REPLACE(SUBSTRING_INDEX(@mailAddress, '@', 1), '.', '') namePart,
    SUBSTRING_INDEX(@mailAddress, '@', -1) domainPart,
    CONCAT(REPLACE(
        SUBSTRING_INDEX(@mailAddress, '@', 1)
    , '.', ''),
    '@',
    SUBSTRING_INDEX(@mailAddress, '@', -1)
) email;

Result:

@mailAddress            namePart  domainPart  email
--------------------------------------------------------------
[email protected]   myname    gmail.com   [email protected]

SQLFiddle here: http://sqlfiddle.com/#!2/d41d8/25514

answered Nov 20, 2013 at 10:52

NoLifeKing

1,94913 silver badges27 bronze badges

Collectives™ on Stack Overflow

Possible with MYSQL replace()?

6 Answers 6

1 Comment

1 Comment

Comments

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

1 Comment

1 Comment

Comments

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related