Postgres Pro
Downloads
Home   >   mailing lists

Thread: SQL UPDATE question

SQL UPDATE question

From
Joshua
Date:
Hi,
Here is a quick and easy one I have a questions about:

I have a table (customers) that has a field (firstname) with the
following data.

firstname
--------------------
Mike H
Josh
Jim B
Katie I
Jeff
Suzy
John R


Can someone provide the syntax for a SQL UPDATE statement that will
remove middle initial in the above column and leave the first name intact?

Please let me know.

Thanks,
Josh

Re: SQL UPDATE question

From
Erik Jones
Date:
On Mar 14, 2008, at 1:30 PM, Joshua wrote:

> Hi,
> Here is a quick and easy one I have a questions about:
>
> I have a table (customers) that has a field (firstname) with the
> following data.
>
> firstname
> --------------------
> Mike H
> Josh
> Jim B
> Katie I
> Jeff
> Suzy
> John R
>
>
> Can someone provide the syntax for a SQL UPDATE statement that will
> remove middle initial in the above column and leave the first name
> intact?

-- notice there is a space before the .* in the regex
UPDATE table
SET firstname = regexp_replace(firstname, ' .*', '');

Erik Jones

DBA | Emma®
erik@myemma.com
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com

Re: SQL UPDATE question

From
"A. Kretschmer"
Date:
am  Fri, dem 14.03.2008, um 13:30:02 -0500 mailte Joshua folgendes:
> Hi,
> Here is a quick and easy one I have a questions about:
>
> I have a table (customers) that has a field (firstname) with the
> following data.
>
> firstname
> --------------------
> Mike H
> Josh
> Jim B
> Katie I
> Jeff
> Suzy
> John R
>
>
> Can someone provide the syntax for a SQL UPDATE statement that will
> remove middle initial in the above column and leave the first name intact?

A little hint, i hopen it is helpful for you:

test=*# select regexp_replace('Mike H', ' .*$','');
 regexp_replace
----------------
 Mike

You can use this regexp_replace() - function in your UPDATE-Command.
Attention: i assume, that there are no space before the first name.


Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net