Обсуждение: trim() spec
Hi, Can you tell me trim() spec, please ? (This problem has been discussed in pgsql-jp ML. ) In trim(trailing 'abc' from '123cbabc') function, 'abc' means ~'[abc]'. pgbash> select trim(trailing 'abc' from '123cbabc'); rtrim ----- 123 <==== it is not "123cb"!! (1 row) In current trim() function, MULTIBYTE string is broken. pgbash> select trim(trailing '0x8842' from '0xB1428842'); --~~ ~~--~~ rtrim -----0xB1 <==== MULTIBYTE string broken (This is a bug.) (1 row) If trim(trailing 'abc' from '123cbabc') returns "123cb", current trim() spec is broken. However, the spec that 'abc' means ~'[abc]' is ugly. It seems that this ugly spec isn't used for any kind of functions argument and SQL expression except for trim(). How do you think about the trim() spec ? -- Regards, SAKAIDA Masaaki -- Osaka, Japan
Can someone comment on this? > Hi, > > Can you tell me trim() spec, please ? (This problem has been > discussed in pgsql-jp ML. ) > > In trim(trailing 'abc' from '123cbabc') function, 'abc' means > ~'[abc]'. > > pgbash> select trim(trailing 'abc' from '123cbabc'); > rtrim > ----- > 123 <==== it is not "123cb"!! > (1 row) > > > In current trim() function, MULTIBYTE string is broken. > > pgbash> select trim(trailing '0x8842' from '0xB1428842'); > --~~ ~~--~~ > rtrim > ----- > 0xB1 <==== MULTIBYTE string broken (This is a bug.) > (1 row) > > > If trim(trailing 'abc' from '123cbabc') returns "123cb", current > trim() spec is broken. However, the spec that 'abc' means ~'[abc]' > is ugly. It seems that this ugly spec isn't used for any kind of > functions argument and SQL expression except for trim(). > > How do you think about the trim() spec ? > > -- > Regards, > SAKAIDA Masaaki -- Osaka, Japan > > > -- Bruce Momjian | http://www.op.net/~candle pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
> Can you tell me trim() spec, please ? (This problem has been
> discussed in pgsql-jp ML. )
> In trim(trailing 'abc' from '123cbabc') function, 'abc' means
> ~'[abc]'.
> If trim(trailing 'abc' from '123cbabc') returns "123cb", current
> trim() spec is broken. However, the spec that 'abc' means ~'[abc]'
> is ugly. It seems that this ugly spec isn't used for any kind of
> functions argument and SQL expression except for trim().
> How do you think about the trim() spec ?
afaict, the SQL92 spec for trim() requires a single character as the
first argument; allowing a character string is a Postgres extension. On
the surface, istm that this extension is in the spirit of the SQL92
spec, in that it allows trimming several possible characters.
I'm not sure if SQL3/SQL99 has anything extra to say on this.
position() and substring() seem to be able to do what you want;
select substring('123ab' for position('ab' in '123ab')-1);
gives '123', while
select substring('123ab' for position('d' in '123ab')-1);
gives '123ab', which seems to be the behavior you might be suggesting
for trim().
- Tom
> > Can you tell me trim() spec, please ? (This problem has been
> > discussed in pgsql-jp ML. )
> > In trim(trailing 'abc' from '123cbabc') function, 'abc' means
> > ~'[abc]'.
> > If trim(trailing 'abc' from '123cbabc') returns "123cb", current
> > trim() spec is broken. However, the spec that 'abc' means ~'[abc]'
> > is ugly. It seems that this ugly spec isn't used for any kind of
> > functions argument and SQL expression except for trim().
> > How do you think about the trim() spec ?
>
> afaict, the SQL92 spec for trim() requires a single character as the
> first argument; allowing a character string is a Postgres extension.
> On the surface, istm that this extension is in the spirit of the SQL92
> spec, in that it allows trimming several possible characters.
>
> I'm not sure if SQL3/SQL99 has anything extra to say on this.
>
> position() and substring() seem to be able to do what you want;
>
> select substring('123ab' for position('ab' in '123ab')-1);
>
> gives '123', while
>
> select substring('123ab' for position('d' in '123ab')-1);
>
> gives '123ab', which seems to be the behavior you might be suggesting
> for trim().
>
> - Tom
Thomas Lockhart <lockhart@alumni.caltech.edu> writes:
>> If trim(trailing 'abc' from '123cbabc') returns "123cb", current
>> trim() spec is broken. However, the spec that 'abc' means ~'[abc]'
>> is ugly. It seems that this ugly spec isn't used for any kind of
>> functions argument and SQL expression except for trim().
> afaict, the SQL92 spec for trim() requires a single character as the
> first argument; allowing a character string is a Postgres extension. On
> the surface, istm that this extension is in the spirit of the SQL92
> spec, in that it allows trimming several possible characters.
MySQL's crashme list has some useful information about this: they
indicate whether an implementation considers a multi-char TRIM argument
to be a set (our way) or a substring (MySQL does it that way, for one).
So there's precedent for both sides.
Given that our trim() code claims to exist for Oracle compatibility,
I'd have assumed that its handling of multi-char arguments followed
Oracle. But the crashme list doesn't show Oracle as supporting either
semantics. Can someone with access to Oracle check this?
> I'm not sure if SQL3/SQL99 has anything extra to say on this.
The 1994 draft specifies just a single trim character, same as SQL92.
Haven't gotten around to grabbing the 99 draft yet...
regards, tom lane
On Tue, Jun 13, 2000 at 10:45:07AM -0400, Tom Lane wrote: > Thomas Lockhart <lockhart@alumni.caltech.edu> writes: > >> If trim(trailing 'abc' from '123cbabc') returns "123cb", current > >> trim() spec is broken. However, the spec that 'abc' means ~'[abc]' > >> is ugly. It seems that this ugly spec isn't used for any kind of > >> functions argument and SQL expression except for trim(). > > > afaict, the SQL92 spec for trim() requires a single character as the > > first argument; allowing a character string is a Postgres extension. On > > the surface, istm that this extension is in the spirit of the SQL92 > > spec, in that it allows trimming several possible characters. > > MySQL's crashme list has some useful information about this: they > indicate whether an implementation considers a multi-char TRIM argument > to be a set (our way) or a substring (MySQL does it that way, for one). > So there's precedent for both sides. > > Given that our trim() code claims to exist for Oracle compatibility, > I'd have assumed that its handling of multi-char arguments followed > Oracle. But the crashme list doesn't show Oracle as supporting either > semantics. Can someone with access to Oracle check this? Oracle 8i gives you an error if you give a multi-character argument to TRIM. So anything that worked with Oracle would work the same with us. Richard