Re: Trim not working (PostgreSQL 9.1.2 on Win64) - Mailing list pgsql-general

On 07/28/2012 03:20 PM, Edson Richter wrote:
> 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).

Well I am a little confused.
First the position marker changed from '-' to '+'
Second when I apply the above I get:
AXA VIGILANCIA for a length of 14.

Given that the substring is from 5 for .. that looks reasonable. Not
sure how you are getting TAXA... That would imply start from 4.

>
>
> Edson.
>
>


--
Adrian Klaver
adrian.klaver@gmail.com