Hi there.
I have a table (employee) with 3 columns (id, name, mail) - there are several thousand records in the table.
The mail column for each record contains either an id number, a name, or an email address.
e.g. '1234567890', 'jsmith', 'mheppleworth', '33442233@ouremail.com', 'jsmith@ouremail.com', 'john.smith@ouremail.com', 'marty.heppleworth@ouremail.com', etc.
I need to query the 'mail' field for each record on the table to see if it ends in '@ouremail.com'; If it doesn't then I need to append '@ouremail.com' on to the end of whatever is in the 'mail' field (there will always be data as the column is NOT NULL). The intention is to run this query to set every record to how it should be, and then to re-run the query on a regular basis to correct any newly created 'bad' records.
I'm stuck on how to query each record & then update it if needed. Can anyone help, or set me off in the right direction with this, please? My thought was to search for '@' in the field and then if not found to append '@ouremail.com', but I'm not sure that this is the most efficient solution. Also, I'm not sure how to append '@ouremail.com' in the correct place as there is no fixed length for the data in the field.
I would appreciate any help/advice with this.
Thanks.