From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | mark(dot)pether(at)gmail(dot)com |
Cc: | pgsql-bugs(at)postgresql(dot)org |
Subject: | Re: BUG #14441: trim function bug (hit a keyword somewhere internally) |
Date: | 2016-11-30 06:24:04 |
Message-ID: | 17020.1480487044@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
mark(dot)pether(at)gmail(dot)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=# select TRIM(LEADING 'ABC' FROM 'CAABC001');
ltrim
-------
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=# select substring('C001' from '^(?:EXCLUDE)*(.*)$');
substring
-----------
C001
(1 row)
regression=# select substring('EXCLUDEC001' from '^(?:EXCLUDE)*(.*)$');
substring
-----------
C001
(1 row)
regression=# select substring('EXCLUDEEXCLUDEC001' from '^(?:EXCLUDE)*(.*)$');
substring
-----------
C001
(1 row)
See
https://www.postgresql.org/docs/current/static/functions-matching.html#FUNCTIONS-POSIX-REGEXP
regards, tom lane
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2016-11-30 06:51:36 | Re: BUG #14441: trim function bug (hit a keyword somewhere internally) |
Previous Message | mark pether | 2016-11-30 06:16:16 | Re: BUG #14441: trim function bug (hit a keyword somewhere internally) |