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

Em 28/07/2012 20:18, Adrian Klaver escreveu:
> 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.
>>
>>
>
>
Right, I've posted the expression for negatives over the line example
with positive.
The correct (as your assumption) is that when (+), then start at 4. When
(-), then start at 5.
Anyway, I'm checking Tom Lane query about type of space (since \s works,
make sense to be something else than space character as I would expect).

Thanks,

Edson.