SQL, replace part of a string with email

Question

In my DB I have a column user_email with values:

aaa@test.com
bbb@test.com
ccc@test.com

I would only like to change part of email address that comes after @, so that the resulting column would have values:

aaa@other.net
bbb@other.net
ccc@other.net

How could I achieve that?

Answer 1

You might use replace, substr and instr together as :

SELECT replace( 'aaa@test.com',
         substr('aaa@test.com',instr('aaa@test.com','@'),length('aaa@test.com'))
                              ,'@other.net') as result_str;

 result_str
-------------
aaa@other.net

or from your table(tab) with a column called as email :

select replace(email,substr(email,instr(email,'@'),length(email)),'@other.net') result_str
  from tab;

result_str
-------------
aaa@other.net
bbb@other.net
ccc@other.net

Rextester Demo

Answer 2

select concat
        (substring
          ('bbb@test.com',1,char_length
            ('bbb@test.com')-
            char_length
              (substring_index
                ('bbb@test.com','.',-1))),'net') x;

| x            |
| ------------ |
| bbb@test.net |

---

View on DB Fiddle

Answer 3

I've found following solution that seems to do the trick:

UPDATE table_name SET user_email = REPLACE(user_email, '@test.com', '@other.net');

Answer 4

use SUBSTRING_INDEX and concat fnction

select concat(SUBSTRING_INDEX("aaa@test.com", "@", 1),'@other.net')

output aaa@other.net

so for your column user_email it would be

select concat(SUBSTRING_INDEX(user_email, "@", 1),'@other.net')

Answer 5

use replace function

demo

select replace(name,substring(name,position('@' in name),length(name)-position('@' in name)+1),'@other.net')

select replace('aaa@test.com',substring('aaa@test.com',position('@' in 'aaa@test.com'),
length('aaa@test.com')-position('@' in 'aaa@test.com')+1),'@other.net')

output:

val             n
aaa@test.com    aaa@other.net