Re: using a correlated subquery in update

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/