sql – Replace part of a string in an entire column

Question:

My question is the following:

I have a column with emails of the style "mail@example.com" and I want to replace all the records that have the domain "@ example.com" with "@ example.es".

Therefore, if we previously had this data

correo1@ejemplo.com
correo2@mantener.com 
correo3@ejemplo.com

Now they should look like this:

correo1@ejemplo.es  
correo2@mantener.com
correo3@ejemplo.es

Does anyone know if it is possible? PS: It does not help me to do a REPLACE because as I have been seeing it saves the records in another table.

Thanks and best regards.

Answer:

If you can use replace , in an update statement, to update the same field in the table, for example:

create table #prueba (
  correo nvarchar(300)
);

insert into #prueba
values ('correo1@ejemplo.com correo2@mantener.com correo3@ejemplo.com')
     , ('correo7@ejemplo.com');

update #prueba
   set correo = replace(correo, '@ejemplo.com', '@ejemplo.es');

select *
  from #prueba;

It returns this to us:

correo
-------------------------------------------------------------------------------------    
correo1@ejemplo.es correo2@mantener.com correo3@ejemplo.es
correo7@ejemplo.es

(2 rows affected)