From: Steve Crawford <scrawford@pinpointresearch.com>
To: Carlos Mennens <carlos.mennens@gmail.com>
Cc: PostgreSQL (SQL) <pgsql-sql@postgresql.org>
Subject: Re: Update Mass Data in Field?
Date: Thu, 26 Jan 2012 16:06:43 -0800
Message-ID: <4F21EA93.9090202@pinpointresearch.com> (raw)
In-Reply-To: <CAAQLLO5fHwYyjtzDs98VAn6FwMW3dnp+uRYYX7-H11OA-WBqnw@mail.gmail.com>
References: <CAAQLLO5fHwYyjtzDs98VAn6FwMW3dnp+uRYYX7-H11OA-WBqnw@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: scrawford@pinpointresearch.com, carlos.mennens@gmail.com
Subject: Re: Update Mass Data in Field?
In-Reply-To: <4F21EA93.9090202@pinpointresearch.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox