From: Tom Lane <tgl@sss.pgh.pa.us>
To: mark.pether@gmail.com
cc: pgsql-bugs@postgresql.org
Subject: Re: BUG #14441: trim function bug (hit a keyword somewhere
 internally)
In-reply-to: <20161130011710.6539.53657@wrigleys.postgresql.org>
References: <20161130011710.6539.53657@wrigleys.postgresql.org>
Comments: In-reply-to mark.pether@gmail.com
	message dated "Wed, 30 Nov 2016 01:17:10 +0000"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <17019.1480487044.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Wed, 30 Nov 2016 01:24:04 -0500
Message-ID: <17020.1480487044@sss.pgh.pa.us>
Precedence: bulk
Sender: pgsql-bugs-owner@postgresql.org

mark.pether@gmail.com writes:
> select TRIM(LEADING 'EXCLUDE' FROM 'C001');
> Will incorrectly strip off the 'C' leaving '001'.

The code appears to believe that the first argument is a set of characters
that are to be removed from the front of the string argument until none
remain.  For instance:

regression=3D# select TRIM(LEADING 'ABC' FROM 'CAABC001');
 ltrim=20
-------
 001
(1 row)

So the behavior you mention is expected.

I think what you're expecting is to strip off one or more occurrences of a
substring, but that's not what our version of TRIM does.

A look at the SQL standard indicates that it requires the first argument
to be *exactly one* character in length, and then TRIM strips leading
occurrence(s) of that character.  So our existing implementation is a
plausible extension of that ... as is your expectation, but there's
certainly no reason to favor yours over what's there.  Also:
* the PG code has acted this way since it was introduced in 1997.
* the code comments claim, and a bit of googling seems to confirm,
that this matches Oracle's behavior.
So I do not see us changing this.

You can easily build the behavior you want out of other spare parts.
Regexes would be my first weapon of choice:

regression=3D# select substring('C001' from '^(?:EXCLUDE)*(.*)$');
 substring=20
-----------
 C001
(1 row)

regression=3D# select substring('EXCLUDEC001' from '^(?:EXCLUDE)*(.*)$');
 substring=20
-----------
 C001
(1 row)

regression=3D# select substring('EXCLUDEEXCLUDEC001' from '^(?:EXCLUDE)*(.*=
)$');
 substring=20
-----------
 C001
(1 row)

See
https://www.postgresql.org/docs/current/static/functions-matching.html#FUNC=
TIONS-POSIX-REGEXP

			regards, tom lane


--=20
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs