On 01/26/2012 03:59 PM, Carlos Mennens wrote:
I'm new to SQL so I'm looking for a way to change several email
addresses with one command. For example everyone has a 'holyghost.org'
domain and I need to change a few 100 email addresses in the field
'emp_email'. I need to UPDATE employees table which has a COLUMN
'emp_email' and change %holyghost.org to %ghostsoftware.com.
I tried:
UPDATE employees SET emp_email = '%ghostsoftware.com' WHERE emp_email
LIKE '%holyghost.org';
It didn't update anything when I ran the command. Does anyone know how
I need to correct my SQL statement above to change everyone's email
address?
...set emp_email = regexp_replace(emp_email, '@holyghost.org$',
'@ghostingsoftware.org') where emp_email ~ '@holyghost.org$'...
This is using the regular expression match and regexp_replace to ensure
that the pattern is anchored at the end of the field and includes the
"@" sign in the expression to avoid accidentally matching something like
...@theholyghost.org.
You can always do a select of the emp_email alongside the replacement
expression to be sure it will do what you want before actually updating
your database.
Cheers,
Steve
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
