New email address convention

Question

I have a database with email addresses in it.

My company is changing our email address convention from:

first_initiallast_name@mycompany.com

to

first_name.last_name@contoso.com

I'd like to write a SQL statement to update all the email addresses in one shot in this database. First and last name are columns in the same table (we'll call it MY_TABLE for simplicity's sake).

How could I do this in an Oracle SQL statement?

That's relatively straight-forward to do, have you tried anything? — LittleBobbyTables - Au Revoir, Jan 20 '14 at 19:42

score 1 · Accepted Answer · answered Jan 20 '14 at 19:41

1

It seems like you'd just want

UPDATE my_table
   SET email_address = first_name || '.' || last_name || '@contoso.com'

That will update every row in the table and assumes that you have no NULL first or last name values.

answered Jan 20 '14 at 19:41

Justin Cave

227,342
24
367
384

2

you will also need to handle space, apostrophe, and potentially other characters in the first_name and last_name. – BWS Jan 20 '14 at 19:43
Also handle letter's case. – Machado Jan 12 '18 at 20:25

score 0 · Answer 2 · edited May 23 '17 at 12:03

You juste want to update the email field with two others fields:

UPDATE my_table SET email= first_name || '.' || last_name || '@contoso.com'
    WHERE first_name != NULL AND last_name != NULL

Be aware that the transformation might be incorrect if first_name or last_name is empty...

EDIT: In reality what you want is similar to this question: SQL UPDATE SET one column to be equal to a value in a related table referenced by a different column?

New email address convention

2 Answers2