Re: Trim not working (PostgreSQL 9.1.2 on Win64)

Em 28/07/2012 13:46, Adrian Klaver escreveu:
> On 07/27/2012 04:58 PM, Edson Richter wrote:
>> I've a select with the following expression:
>>
>> select trim(both ' ' from substring(rslinha2 from 5 for position('(-)'
>> in rslinha2)-6))
>> from ...
>>
>> problem is that the spaces are not being removed from either side. What
>> would be wrong?
>
> FYI the '' is redundant, empty spaces are removed by default.
> Some examples of what is in rslinha2 before the above and what you are
> seeing after would help.
>
>>
>> Thanks for your help,
>> --
>>
>> *Edson Carlos Ericksson Richter*
>> /SimKorp Informática Ltda/
>> Fone: (51) 3366-7964
>> Celular: (51)9318-9766/(51) 8585-0796
>> Embedded Image
>>
>>
>
>
Yes, I know the redundancy. Nevertheless, it did not work.
The information about the data follows:

rslinha2 character varying

content for rslinha2 (this is one of several, but they are similar -
data has been imported into this field from file using foreign table
with file_fdw):

"2. TAXA VIGILANCIA (+) R$ 13,00"

resulting substring expression is:

" TAXA VIGILANCIA "

(1 space at beginning, and several after)

If I do apply trim over the substring, no spaces are removed, and I
cannot understand why.

Complete (not working) expression are:

-------------------------------------------------------------------------------------------------------------------------
select trim(both ' ' from substring(rslinha2 from 5 for position('(+)'
in rslinha2)-6))
from ...
-------------------------------------------------------------------------------------------------------------------------

or

-------------------------------------------------------------------------------------------------------------------------
select trim(substring(rslinha2 from 5 for position('(+)' in rslinha2)-6))
from ...
-------------------------------------------------------------------------------------------------------------------------

But using the regular expression matching "^\s*" and "\s*$" works, and
spaces are removed:

-------------------------------------------------------------------------------------------------------------------------
select regexp_replace(regexp_replace(substring(rslinha2 from 5 for
position('(+)' in rslinha2)-6)), '^\s*', ''), '\s*$', '');
-------------------------------------------------------------------------------------------------------------------------

Maybe I've hit a bug in Postgres, or just I could not fully understand
the usage for trim (I admit, I was expecting trim to behave like in MS
SQL or Java).

Edson.