Update Email in Mysql table

Question

I have lots of entries in my table USERS. Its contains up to 500 email.

Example:

rskidmore@soundviewprep.org
53e935da@mail.com
james.vh@naturescomfortllc.com
arlie@lottapop.com
gigabite10@att.net

I want to updated all email with "1" postfix. Output should be

rskidmore@soundviewprep1.org
53e935da@mail1.com
james.vh@naturescomfortllc1.com
arlie@lottapop1.com
gigabite10@att1.net

Is this possible with single query with replacing last occurrence of "." ???

I don't think it can be done with a query alone. You may need to use PHP to put the `1` in the right place — asprin, Jun 24 '14 at 12:01
@paradox its very much possible try this `select replace('rskidmore@soundviewprep.co.uk.id', substring_index(substring_index('rskid.mo.re@soundviewprep.co.uk.id','@',-1),'.',1), concat(substring_index(substring_index('rskid.mo.re@soundviewprep.co.uk.id','@',-1),'.',1),'1') ) as result ;` — Abhik Chakraborty, Jun 24 '14 at 12:38

hellcode · Answer 1 · 2014-06-24T13:26:55.077

1

You could do this:

UPDATE `USERS` SET `email` = CONCAT( SUBSTRING(`email`, 1, LENGTH(`email`) -1 -LENGTH(SUBSTRING_INDEX(`email`, '.', -1))), '1.', SUBSTRING_INDEX(`email`, '.', -1));

If you have to consider that "co.uk" must be an unbreakable unit, you could use this (and may add more of such subdomain constellations if you want):

UPDATE `USERS` SET `email` = IF(
SUBSTRING(`email`,-6)=".co.uk"
, CONCAT(SUBSTRING(`email`,1,LENGTH(`email`)-6), "1.co.uk")
, CONCAT(SUBSTRING(`email`,1,LENGTH(`email`)-1-LENGTH(SUBSTRING_INDEX(`email`, '.', -1))), '1.', SUBSTRING_INDEX(`email`, '.', -1))
);

edited Jun 24 '14 at 13:26

answered Jun 24 '14 at 12:23

hellcode

2,678
1
17
21

Try this `select CONCAT( SUBSTRING('rskid.mo.re@soundviewprep.co.uk', 1, LENGTH('rskid.mo.re@soundviewprep.co.uk') -1 -LENGTH(SUBSTRING_INDEX('rskid.mo.re@soundviewprep.co.uk', '.', -1))), '1.', SUBSTRING_INDEX('rskid.mo.re@soundviewprep.co.uk', '.', -1)) as res ;` – Abhik Chakraborty Jun 24 '14 at 12:32
@Manwal if you take `rskid.mo.re@soundviewprep.co.uk` as your email the above will give you `rskid.mo.re@soundviewprep.co1.uk` and you have no control on email ids it could be anything. – Abhik Chakraborty Jun 24 '14 at 12:43
@AbhikChakraborty: yours is working for xyz@something.co.uk but not for xyz@a.b.c.company.net – hellcode Jun 24 '14 at 12:44

mahesh · Answer 2 · 2014-06-24T13:57:46.473

0

Its very simple Using CONCAT() , LENGTH() and SUBSTRING_INDEX(). I have tested below query

UPDATE 
  your_table 
SET
  email = CONCAT(
    SUBSTRING_INDEX(email, '.', 1),
    '1.',
    IF(
      (
        LENGTH(email) - LENGTH(REPLACE(email, '.', ''))
      ) = 2,
      SUBSTRING_INDEX(email, '.', - 2),
      SUBSTRING_INDEX(email, '.', - 1)
    )
  ) ;

You can refer here, The above mysql functions

This solution even works good with

test@test.com             test@test1.com
test@yahoo.in             test@yahoo1.in
test@happy.co.in          test@happy1.co.in

edited Jun 24 '14 at 13:57

answered Jun 24 '14 at 12:21

mahesh

1,311
1
13
26

This will fail for `select CONCAT(SUBSTRING_INDEX('rskid.mo.re@soundviewprep.co.uk', '.', 1),'1.',SUBSTRING_INDEX('rskid.mo.re@soundviewprep.co.uk', '.', -1)) as res ;` – Abhik Chakraborty Jun 24 '14 at 12:30
Yes correct @AbhikChakraborty. its not working for james.vh@naturescomfortllc1.com. – Manwal Jun 24 '14 at 12:33
yes try to match with @ since an email can have one @ and then do the replacement. – Abhik Chakraborty Jun 24 '14 at 12:37
@Abhik Chakraborty How is my solution? – mahesh Jun 24 '14 at 13:56

score -1 · Answer 3 · edited May 23 '17 at 12:20

-1

The best way is to use a scripting language which could easily do this, splitting the string, adding '1' and then joining them back. I dunno if its possible only with SQL. This is the append in sql. SQL query to prepend prefix to existing value in a field Also you can use the LIKE construct for a regex to identify the links . Also Update a column of a table with append some values to the same column value in MySQL. I think you will have to use these to create a work-around

edited May 23 '17 at 12:20

Community

1
1

answered Jun 24 '14 at 12:05

paradox

377
3
12

This is not i want. :( – Manwal Jun 24 '14 at 12:20

Abhik Chakraborty · Accepted Answer · 2014-06-24T12:41:29.727

Yes this could be done as

select 
replace('james.vh@naturescomfortllc.com',
       substring_index(substring_index('james.vh@naturescomfortllc.com','@',-1),'.',1),
       concat(substring_index(substring_index('james.vh@naturescomfortllc.com','@',-1),'.',1),'1')
)

Here are few tests

mysql> select 
    -> replace('james.vh@naturescomfortllc.com',
    ->        substring_index(substring_index('james.vh@naturescomfortllc.com','@',-1),'.',1),
    ->        concat(substring_index(substring_index('james.vh@naturescomfortllc.com','@',-1),'.',1),'1')
    -> ) as result ;
+---------------------------------+
| result                          |
+---------------------------------+
| james.vh@naturescomfortllc1.com |
+---------------------------------+
1 row in set (0.00 sec)


mysql> select 
    -> replace('rskidmore@soundviewprep.org',
    ->        substring_index(substring_index('rskidmore@soundviewprep.org','@',-1),'.',1),
    ->        concat(substring_index(substring_index('rskidmore@soundviewprep.org','@',-1),'.',1),'1')
    ->        
    -> ) as result ;
+------------------------------+
| result                       |
+------------------------------+
| rskidmore@soundviewprep1.org |
+------------------------------+


mysql> select 
    -> replace('rskid.mo.re@soundviewprep.co.uk',
    ->        substring_index(substring_index('rskid.mo.re@soundviewprep.co.uk','@',-1),'.',1),
    ->        concat(substring_index(substring_index('rskid.mo.re@soundviewprep.co.uk','@',-1),'.',1),'1')
    ->        
    -> ) as result ;
+----------------------------------+
| result                           |
+----------------------------------+
| rskid.mo.re@soundviewprep1.co.uk |
+----------------------------------+
1 row in set (0.00 sec)

So here is the update command

update your_table set email = 
replace(
 email,
 substring_index(substring_index(email,'@',-1),'.',1),
 concat(substring_index(substring_index(email,'@',-1),'.',1),'1')
) ;

I have tested this update command its giving "test1@test1.com" for "test@test.com". Fiddle - http://www.sqlfiddle.com/#!2/6ed6bc/1. why it so? — Manwal, Jun 24 '14 at 12:48
Hmm I know, its trying to replace the word test with test1 so once you have test@test.com both are getting replaced, which is very unlikely to happen for a real email id, you may never see an email id as `yahoo@yahoo.com`,`gmail@gmail.com` — Abhik Chakraborty, Jun 24 '14 at 12:51
@Manwal: Why is this the best answer? Its wrong. A "xyz@a.b.c.company.net" will be set to "xyz@a1.b.c.compa1ny.net" by this statement. — hellcode, Jun 24 '14 at 14:58
@hellcode I looked more into it and there is no end if you try your solution with `xyz@a1.b.c.compa1ny.co.net` , it fails. My solution will fail for the one you specified. In fact it fails for any email id @co.uk or similar. — Abhik Chakraborty, Jun 25 '14 at 17:17

Update Email in Mysql table

4 Answers4