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?

Answer 1

Try this

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

Answer 2

Try this:

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

Answer 3

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]         │
└──────────────────────┴─────────────────────┘

Answer 4

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

Answer 5

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

Answer 6

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