From: | Michael Fuhr <mike(at)fuhr(dot)org> |
---|---|
To: | Andrew Hammond <ahammond(at)ca(dot)afilias(dot)info> |
Cc: | Sean Davis <sdavis2(at)mail(dot)nih(dot)gov>, Postgres <pgsql-novice(at)postgresql(dot)org> |
Subject: | Re: using a correlated subquery in update |
Date: | 2004-12-03 21:22:18 |
Message-ID: | 20041203212218.GA34367@winnie.fuhr.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
On Fri, Dec 03, 2004 at 02:46:16PM -0500, Andrew Hammond wrote:
> It sounds like he just wants to truncate the '.11' from the end of a string.
We don't know if all strings match that pattern exactly or if it
was just an example. He might also need to strip '.5' and '.6789'.
> UPDATE foo
> SET xm_value = substring(xm_value FOR char_length(xm_value) - 3)
> WHERE xm_value LIKE '%.11';
That covers a specific case. Here are some more general solutions
that might work (or might not, depending on the string format):
substring(xm_value FROM '(.+)\\.[0-9]+$')
rtrim(rtrim(xm_value, '0123456789'), '.')
split_part(xm_value, '.', 1)
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
From | Date | Subject | |
---|---|---|---|
Next Message | Keith Worthington | 2004-12-03 23:16:32 | multiple table join and sumation |
Previous Message | Sean Davis | 2004-12-03 20:06:08 | Re: using a correlated subquery in update |